How to Highlight Every Other Row in Excel (Conditional Formatting & VBA)

Working with rows after rows of data can sometimes get monotonous. Often times, it becomes difficult to differentiate one row from the other.

The solution?

Highlighting (or shading) alternate rows have been proven to make your data more readable and easier on the eyes.

In this tutorial, we will show you three ways in which you can highlight every other row in Excel.

You will see how you can use some built-in Excel tools, like Conditional Formatting and Banded Rows Table Styles. In addition to that, you will also see how you can use a simple VBA macro code to quickly highlight alternate rows of your data.

Table of Contents

How to Highlight Every Other Row using Conditional Formatting

Suppose you have the dataset as shown below and you want to highlight alternate rows in this dataset.

Below are the steps to shade alternate rows in a dataset:

Select the range of cells you want to apply the effect to. You don’t need to select alternating rows, just select the entire area with all the cells.
From the Home tab, select Conditional Formatting (Under the ‘Styles’ group).
From the popup menu that appears, select New Rule. This will open the ‘New Formatting Rule’ dialog box.
Select the option ‘Use formula to determine which cells to format’ from the options under ‘Select a Rule Type’. This will open up a box below where you can enter details about the formula you want to use.
In the input box under ‘Format values where this formula is true’, type the following formula: =MOD(ROW(),2)=0.
Click the Format button. 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. That means you can change the font, the border, or the fill color of only your required cells.
Click the Fill tab and select the color you want to use for your selected cells. It is advisable to use lighter colors so that your text is visible.
Click OK to close the ‘Format Cells’ dialog box.
Click OK again to close the ‘New Formatting Rule’ dialog box.

You will find your selected formatting applied to all the alternate rows of your selected cells.

So, how did this happen?

It’s quite simple, really. 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.

The MOD() function basically finds the remainder when a number is divided by another. When a number is divisible by 2, the remainder is always 0. That means all even numbers will leave a remainder of 0 when divided by 2.

The ROW() function refers to the row of the active cell. 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).

By using Conditional formatting with this formula, you are making sure that only the cells that satisfy the given formula get highlighted.

Note that with the above technique, your highlighting will start from the second row onwards. If you want to start highlighting every alternate row starting from the first row, you need to use the formula:

=MOD(ROW(),2)=1

This will ensure that only every odd-numbered row gets highlighted.

With the above technique, you can even select every 3rd,4th, or nth row of your selection. All you need to do is change the formula according to your requirement.

So, if you want to highlight every 3rd row, you can use the formula: = MOD(ROW(),3)=0. Similarly, to highlight every 4th row, you can use the formula: =MOD(ROW(),4)=0, and so on.

The best thing about using this method is that automatic banding of alternate rows will continue even when you make changes like sort, delete or add new rows to the formatted range of cells!

Also read: Excel Shortcut For Fill Color

How to Highlight Every Other Row using Banded Rows Table Styles

A much easier (and faster) way to highlight alternate rows is by applying the built-in table styles to your cell range.

Excel comes with a large number of predefined table styles that you can use to your advantage. Here are the steps that you need to follow:

Select the range of cells you want to apply the effect to.
Click on Table from the Insert tab or press the CTRL+T keys on your keyboard. This will convert your selected cells into an Excel table. Excel tables come included with automatic filtering of rows and highlights on alternate rows by default. If you still don’t see alternate rows highlighted, you can go to the Design tab and check the Banded Rows option in the Table Style Options group.
Now, if you’re not comfortable with having your cell range converted to a table, you can choose to convert the table back to the range. This will keep the alternate row highlights but will get rid of the filters. To convert the table back to the range, you need to click on the Design tab and click ‘Convert to Range’ from the Tools group (towards the left of the menu ribbon).

However, do remember that when you convert your table back to the range, the automatic color banding will not happen when you add or delete new rows to the range. Your color bands will travel with the original rows and your zebra pattern will get distorted.

Now, what if you don’t want the blue and white pattern that Excel provides you by default?

There are a number of patterns and colors that you can use to style your table rows. All you need to do is select your table, go to Table Styles from the Design tab (towards the right of the menu ribbon) and select the color formatting that you like.

You can use arrow buttons to scroll through more available styles or click on the More button ()to view all the styles altogether. Select the style and color combination you like and the change will be reflected in your table.

Want the color scheme even more customized? Go ahead and create your own style. Here’s how:

Select any cell in your table.
From the Table Style options (of the Design tab), click on the More button () on the right of all the style options, and select ‘New Table Style’. This will open the ‘New Table Style’ dialog box.
You can choose to give your customized table style a new name or continue with the default name provided.
Select ‘First Row Stripe’ from the ‘Table Element’ options.

Click on the Format button and select the Fill tab from the ‘Format Cells’ dialog box.
Select whatever color you want your alternate rows to have. You can even click on ‘Fill Effects’ to add gradient effects or, if you’re feeling particularly adventurous, you can add patterns to your alternate rows too! Anytime you want to revert, just click on the Clear button.
Once you are done selecting a Fill format you like, click OK.
Finally, click OK to close the ‘New Table Style’ dialog box.

Your customized style will now get added to the Style gallery. Select it from the Table Style options under your Design tab and Voila! You get your customized highlights!

If you want to highlight every 3rd, 4th or any other numbers of rows, then do the following:

Select any cell in your table.
From the Table Style options (of the Design tab), right-click any style option that you like and select ‘Duplicate’ from the popup menu. This will open the ‘Modify Table Style’ dialog box.
You can choose to give your customized table style a new name or continue with the default name provided.
Select ‘Second Row Stripe’ from the ‘Table Element’ options.
Under ‘Stripe Size’ (towards the right side of the dialog box), select the number of rows you want to skip. So, if you want to highlight every 3rd row, select 3, and so on.
Click OK to close the dialog box. Your customized style will now get added to the Style gallery. Select it from the Table Style options under your Design tab and that’s all! Your table will now have your customized highlights.

Also read: Change Cell Color Based on Value of Another Cell in Excel

How to Highlight Every Other Row using VBA

Although the above two methods are good enough to help you give your spreadsheets the face-lift you need, there’s one more way that you might like, if you prefer coding and are comfortable using VBScript.

The technique is quite simple and so is the code. In fact, here’s the code that you need:

Sub highlight_alt_rows()
Dim Rng As Range
Set Rng = Selection
For i = Rng.Rows.Count To 1 Step -2
Set myRow = Rng.Rows(i)
myRow.Style = "Note"
Next i
End Sub

Select the range of cells for which you want alternate rows highlighted. To use this, code, do the following:

From the Developer Menu Ribbon, select Visual Basic.
Once your VBA window opens, Click Insert->Module. Now you can start coding. Type or copy-paste the above lines of code into the module window.
Run the macro by navigating to Developer->Macros->select_alt_cells->Run or just clicking the button.
When you go back to your spreadsheet, you will find the selected range formatted with alternating highlighted rows.

Explanation of the Code

In the above code, we have used a ‘For’ loop to cycle through every second row in the selected range. That is why we added the “Step 2” in line 4. For every second row, we applied the style “Note”. This style gives the corresponding row a light peachy-colored highlight.

You have a number of other options for the style you want to use. Experiment with different styles like “Neutral”, “Accent1”, “Accent2”, etc.

To see what options you can use, navigate to your Styles gallery under your Worksheet’s Home tab. The Styles gallery will be towards the right side of the Home tab’s ribbon. Here, you will find the different Styles available.

You can even customize this by creating a ‘New Cell Style’.

An Alternate VBA Code

An alternate way to change the color of your highlighted rows would be to use the Interior.color property of your cell range. For this, you simply need to change line 6 of the above code. You need to know the RGB value of the color you want. Say, you want a light plum color for your highlighted rows. The RGB value for this color is RGB(221, 160, 221).

To apply this to your selection, change line 6 of the above code to:

myRow.Interior.Color = RGB(221, 160, 221)

After that, run the Macro and you will see your selected range formatted with alternating highlighted rows of your favorite color!

You can find out the RGB value of any color by referring to this link: https://www.rapidtables.com/web/color/RGB_Color.html

To highlight every nth row of your selection, you need to make a slight change to the above code. Simply change the Step in line 4 from the number 2 to the number of rows you want your highlights to skip.

So if you want to skip 3 rows, your line 4 will be:

For i = Rng.Rows.Count To 1 Step -3

Therefore, to apply a light plum color to every third row of your cells, your code will be:

Sub highlight_third_rows()
Dim Rng As Range
Set Rng = Selection
For i = Rng.Rows.Count To 1 Step -3
Set myRow = Rng.Rows(i)
myRow.Interior.Color = RGB(221, 160, 221)
Next i
End Sub

Conclusion

In this tutorial, we looked at three different strategies that you can use to highlight alternating rows in Excel.

For each strategy, we also showed you how you can customize your highlights by using different styles and colors as well as how you can highlight every 3rd, 4th, or nth row of your selected range.

We hope you found this tutorial helpful and that you will use our techniques and experiment with the different highlighting styles discussed.

Other Excel tutorials you may find useful: