How to make every other Row or Column shaded in Excel?

How to set color in alternate Rows?

In Excel, alternate Rows/columns can be shaded using one of these 3 methods.

1. Conditional Formatting
2. Vba code to color alternative Excel rows [More Customization possible]
3. Converting Data rows to Table [Easiest option]

Conditional Formatting to Color alternate Rows/Columns

Select the cells or rows that you would like to have color shades alternatively.

Now follow these steps …

1. Select on Menu ->Styles Tab -> Conditional Formatting
2. Click ‘New Rule’ -> ‘Use a formula to determine which cells to format’
3. Type formula ‘=(MOD(ROW(),2)=1)’ in Input text box.
4. Choose ‘Format’ & set a background color
5. Click ‘Ok’.

Now Excel will color the chosen rows with background color shaded in alternate rows.

You can see that the formula ‘=(MOD(ROW(),2)=1)’ will return true for alternate rows and hence the coloring is done the way as how you needed.

Similarly, alternate columns can be shaded by changing the formula to ‘=(MOD(COLUMN(),2)=1)’

Now, if you want to have more control over the coloring, then below programmatic method can be followed.

Get VBA Code to Color alternate Rows/Columns

This VBA code will change the alternate rows in color with light blue color and does this for 1000 rows.

Change the number of rows & color index in the below code before using it.

'Macro to make every other Row or Column shaded in Excel
Sub Color_Alternate_Rows_Columns()
    'Declare Variables
    Dim iColorCode
    Dim i As Double
    Dim nRows As Double
    Dim bColor As Boolean
    
    'Initialize
    iColorCode = rgbLightBlue
    nRows = 1000
    
    'Loop to Color alternate Rows/Columns
    bColor = True
    For i = 1 To nRows
        If bColor Then
            'Set Color to Alternate Row
            ActiveSheet.Rows(i & ":" & i).Interior.Color = iColorCode
            
            'Set Color to Alternate Column
            ActiveSheet.Columns(i).Interior.Color = iColorCode
            bColor = False
        Else
            bColor = True
        End If
    Next i
End Sub

The number of rows can be modified to match the last data row.

Also, this modules works on active sheet and any existing color formats will be lost.

Shaded Rows/Columns using Table Branded Rows/Columns

Select the data cells that you would like to have alternate branded colors. Click Menu->Insert->Table.

Now, select Design in menu & choose “Branded Rows” or “Branded Columns” in Table Style options tab. After that , you can try changing the styles in the adjacent “Table Styles” Tab.

Out of all 3, this using table option is pretty much easy. Also, it gives additional option to have the table format more build in options like summary Total, Rows/Columns total etc.,