Vba shade alternative rows in Excel

For example, in the table below, I would like to shade alternate rows.

excel vba shade alternative rows

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub ShadeAlternateRows()
  3. Dim i As Integer
  4. ''
  5. For i = 5 To 12
  6. If i Mod 2 = 0 Then
  7. ActiveSheet.Range(ActiveSheet.Cells(i, 2), ActiveSheet.Cells(i, 8)).Select
  8. With Selection.Interior
  9. .Pattern = xlSolid
  10. .PatternColorIndex = xlAutomatic
  11. .ThemeColor = xlThemeColorAccent6
  12. .TintAndShade = 0.799981688894314
  13. .PatternTintAndShade = 0
  14. End With
  15. End If
  16. Next i
  17. 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.


You can find similar Excel Questions and Answer hereunder

1) How can I set FreezePanes in a certain range using VBA?

2) How can I turn off Alerts using VBA?

3) How to rename multiple sheets easily with VBA

4) How can I update a listbox based on data in a list using VBA?

5) How can I check if a file exists in a folder using VBA?

6) How do I enter a formula in a cell using VBA (using Absolute Reference)?

7) How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?

8) How can I add a Timestamp after macro execution?

9) How to add a link in a sheet to another sheet

10) How can I find the last used cell in a Column in VBA?


Here the previous and next chapter