Excel Guides

How to shade based on odds and evens in Excel

There are a few different ways that you can shade based on odds and evens in Excel. One way is to use the conditional formatting feature. To do this, first select the cells that you want to apply the shading to. Then, go to the Home tab and click on Conditional Formatting in the Styles group. In the drop-down menu, choose New Rule. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format. In the Format values where this formula is true text box, type =MOD(ROW(),2)=0. This formula will shade every other row starting with row 2 (the first row is row 1). Click on the Format button and choose the fill color that you want to use. Then click OK twice to close out of the dialog boxes.

Another way to shade based on odds and evens is to use a macro. The following macro will shade every other row starting with row 2:

Sub ShadeOddEven()
 Dim i As Integer
 For i = 2 To ActiveSheet.UsedRange.Rows.Count Step 2
  With Rows(i)
   .Interior.ColorIndex = 6 'shade every other row green
  End With
 Next i
End Sub

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.