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.

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 add Trendline to a chart using VBA?

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

3) Is there a way to easily shade alternate rows in a data range for improving readability?

4) How do I enter a formula in a cell using VBA (using Relative Reference)?

5) How can I convert Column numbers into Column names for use in range definition?

6) I have a macro that takes a lot of time for execution - how can I keep the user informed that the macro is running?

7) How can I loop through all ActiveX checkboxes in WorkSheet and set them to Unchecked status?

8) How can I find the number of working days between 2 dates using VBA?

9) How can I dynamically add a hyperlink using VBA?

10) Vba list all files in a folder in Excel

11) How can I add a WorkSheet and name it as required using VBA?

12) How do i put double quotes in a string in vba in Excel

13) How can I hide a sheet completely from users (the sheet should not even appear in Unhide dialog box)?

14) Line break in vba message box in Excel

15) How can I get users to select a folder to save the output of my macro?