This will ensure that only every odd-numbered row gets highlighted. If you want to start highlighting every alternate row starting from the first row, you need to use the formula: =MOD(ROW(),2)=1 Note that with the above technique, your highlighting will start from the second row onwards. What the above formula does is, find the cells belonging to row numbers that are divisible by two (in other words all the even-numbered rows).īy using Conditional formatting with this formula, you are making sure that only the cells that satisfy the given formula get highlighted. The ROW() function refers to the row of the active cell. That means all even numbers will leave a remainder of 0 when divided by 2. When a number is divisible by 2, the remainder is always 0. The MOD() function basically finds the remainder when a number is divided by another. The crux of this whole process is in the formula that you had entered in the ‘ New Formatting Rule’ dialog box : =MOD(ROW(),2)=0. You will find your selected formatting applied to all the alternate rows of your selected cells.
Click OK again to close the ‘ New Formatting Rule’ dialog box.Click OK to close the ‘ Format Cells’ dialog box.It is advisable to use lighter colors so that your text is visible. Click the Fill tab and select the color you want to use for your selected cells.That means you can change the font, the border, or the fill color of only your required cells. This will open the ‘ Format Cells’ dialog box, which will allow you to apply any kind of formatting to the cells that satisfy the above formula. In the input box under ‘ Format values where this formula is true’, type the following formula: =MOD(ROW(),2)=0.This will open up a box below where you can enter details about the formula you want to use. Select the option ‘ Use formula to determine which cells to format’ from the options under ‘ Select a Rule Type’.This will open the ‘New Formatting Rule’ dialog box. From the popup menu that appears, select New Rule.From the Home tab, select Conditional Formatting (Under the ‘Styles’ group).You don’t need to select alternating rows, just select the entire area with all the cells. Select the range of cells you want to apply the effect to.Suppose you have the dataset as shown below and you want to highlight alternate rows in this dataset.īelow are the steps to shade alternate rows in a dataset:
How to Highlight Every Other Row using Conditional Formatting How to Highlight Every Other Row using VBA.
NOTE: For VBA, you can select code in your VBA window, press Tab, then copy and paste that into your post or comment. To keep Reddit from mangling your formulas and other code, display it using inline-code or put it in a code-block This will award the user a ClippyPoint and change the post's flair to solved. OPs can (and should) reply to any solutions with: Solution Verified
Use the appropriate flair for non-questions.Post titles must be specific to your problem.