- Code: Select all
Public Sub ShadeRows()
Const Shade As Long = 15 ' PatternColorIndex
Const FirstShadedRow As Long = 5
Const Grouping As Long = 3 ' First of group is shaded
Const ShadeWidth As Long = 7 ' 0 = entire row
Const TestColumn As String = "A" ' to find the last used row
Dim R As Long
Dim Rng As Range
For R = Cells(Rows.Count, TestColumn).End(xlUp) _
.Row To FirstShadedRow Step -1
If (R - FirstShadedRow) Mod 3 = 0 Then
If Cells(R, 1).Interior.ColorIndex = Shade Then
Exit For
Else
If ShadeWidth Then
Set Rng = Range(Cells(R, 1), Cells(R, ShadeWidth))
Else
Set Rng = Rows(R).EntireRow
End If
Rng.Interior.ColorIndex = Shade
End If
End If
Next R
End Sub
Install the macro in a normal code module ('Module1' by default).
Set the 5 constants to the values you want. You can set the shading color, the first row to shade, each how many rows you want one row shaded, the width of the shading (number of columns or 0 for entire width) and the column that defines the end of the sheet.
The macro will start shading from the bottom of the sheet and stop when a cell is encountered in column A that is already shaded. This is so that you can run the program repeatedly on the same sheet, for example, whenever you have additions to it.
The macro can be called manually (Tools / Macro / Macros - select 'ShadeRows' - press 'Run') or by other code. In the latter case make sure that sheet to be treated is the ActiveSheet.