Excel Made Easy

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:

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

Description:

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?

Vba shade alternative rows in Excel

You can find similar Excel Questions and Answer hereunder

Here the previous and next chapter