How to set color in alternate Rows?
In Excel, alternate Rows/columns can be shaded using one of these 3 methods.
- Conditional Formatting
- Vba code to color alternative Excel rows [More Customization possible]
- 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 …
- Select on Menu ->Styles Tab -> Conditional Formatting
- Click ‘New Rule’ -> ‘Use a formula to determine which cells to format’
- Type formula ‘=(MOD(ROW(),2)=1)’ in Input text box.
- Choose ‘Format’ & set a background color
- 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.,