vba shade alternative rows in Excel
Q171. In Excel, how can I shade alternate rows in color using VBA to make it easier to read voluminous data running into hundreds of rows?
For example, in the table below, I would like to shade alternate rows.
To do it in Excel, here is the answer:
- Option Explicit
- Sub ShadeAlternateRows()
- Dim i As Integer
- For i = 5 To 12
- If i Mod 2 = 0 Then
- ActiveSheet.Range(ActiveSheet.Cells(i, 2), ActiveSheet.Cells(i, 8)).Select
- With Selection.Interior
- .Pattern = xlSolid
- .PatternColorIndex = xlAutomatic
- .ThemeColor = xlThemeColorAccent6
- .TintAndShade = 0.799981688894314
- .PatternTintAndShade = 0
- End With
- End If
- Next i
- End Sub
a) The code loops through the table range. Line 6 "i Mod 2 =0" is the criteria to apply formatting - basically even numbered rows have shading. When this criteria is met, the cell range is shaded.