Format every second selected row VBA

[weasel21]

I have the below code to apply formatting to every second selected row.

It works when i use

Selection.Rows(Counter).Interior.Pattern = xlGray16

but i want to apply different formatting. i want every second row coloured in blue. I have tried the below but it doesnt work. any ideas?

Sub ShadeEveryOtherRow()
    Dim Counter As Integer
LastRowColumnX = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A15:P" & LastRowColumnX).Select
    For Counter = 1 To Selection.Rows.Count
            If Counter Mod 2 = 1 Then
            Selection.Rows(Counter).Interior
                .Pattern = Solid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent5
                .TintAndShade = 0.599993896298105
                .PatternTintAndShade = 0
        End If
    Next
End Sub

 

[footoo]

Can be done with conditional formatting :
https://support.microsoft.com/en-hk/help/919194/how-to-shade-every-other-row-in-excel

 

[Fluff]

You need to use a with statement

Sub ShadeEveryOtherRow()
   Dim Counter As Integer
   LastRowColumnX = Cells(Rows.Count, 1).End(xlUp).Row
   Range("A15:P" & LastRowColumnX).Select
   For Counter = 1 To Selection.Rows.Count
      If Counter Mod 2 = 1 Then
         With Selection.Rows(Counter).Interior
            .Pattern = Solid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent5
            .TintAndShade = 0.599993896298105
            .PatternTintAndShade = 0
         End With
      End If
   Next
End Sub

 

[weasel21]

Thanks that worked

 

[Norie]

Try this.

Sub ShadeEveryOtherRow()
Dim Counter As Long
Dim LastRowCoumnX As Long

    LastRowColumnX = Cells(Rows.Count, 1).End(xlUp).Row

    Range("A15:P" & LastRowColumnX).Select

    For Counter = 15 To LastRowColumnX Step 2
        With Intersect(Range("A:P"), Rows(Counter)).Interior
                .Pattern = Solid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent5
                .TintAndShade = 0.599993896298105
                .PatternTintAndShade = 0
        End With
    Next Counter

End Sub