Use VBA to shade every 3 rows (banding)

[MikeS]

I've seen ways to do this with conditional formatting (http://
www.cpearson.com/excel/banding.aspx), but I'd like to do it with VBA.

How do I modify the below macro to shade alternate groups of 3 rows?
Instead of every other row...

Sub HighlightAltRows()

Selection.Interior.ColorIndex = xlNone

Dim Counter As Integer

For Counter = 1 To Selection.Rows.Count
If Counter Mod 2 = 0 Then
Selection.Rows(Counter).Interior.ColorIndex = 54
End If
Next
End Sub

 

[Jarek Kujawa]

replace

If Counter Mod 2 = 0 Then

with

If Counter Mod 3 = 0 Then

 

[MikeS]

replace

        If Counter Mod 2 = 0 Then

with

        If Counter Mod 3 = 0 Then

that's not quite what I'm looking for. Let me explain better.

If I select a group of cells (A1:M12), I want:

rows 1-3 = no shade
rows 4-6 = shaded
rows 7-9 = no shade
rows 10-12 = shaded

thanks!

 

[Jarek Kujawa]

pls click YES if it helped

Sub HighlightAltRows()

Selection.Interior.ColorIndex = xlNone

Dim Counter As Integer

For Counter = 1 To Selection.Rows.Count Step 3
If Counter Mod 2 = 0 Then
Range(Selection.Rows(Counter), Selection.Rows(Counter +
2)).Interior.ColorIndex = 54
End If
Next
End Sub

 

[Jacob Skaria]

'Replace the If condition with
If (Counter + 2) \ 3 Mod 2 = 0 Then


If this post helps click Yes

 

[MikeS]

pls click YES if it helped

Sub HighlightAltRows()

    Selection.Interior.ColorIndex = xlNone

    Dim Counter As Integer

    For Counter = 1 To Selection.Rows.Count Step 3
        If Counter Mod 2 = 0 Then
            Range(Selection.Rows(Counter), Selection.Rows(Counter +
2)).Interior.ColorIndex = 54
        End If
    Next
End Sub

that worked! thanks!

 

[MikeS]

'Replace the If condition with
If (Counter + 2) \ 3 Mod 2 = 0 Then

If this post helps click Yes

Could I get some more help with this macro? Here is the problem I
have with it now.

Using my original example, it works great

But if I only select cells A1:M10, it still does the same thing.
Meaning that even though I didn't select any of row 11 or 12, they
still get shaded.

Any ideas how to fix that?