Solved: Every other line

[lukecj]

I am trying to use the below macro to skip every other line and highlight it Grey. However, the range of data will be different every time. How can I get the below to loop so it will highlight all of the lines even if the range changes? Thanks for all of your help.

[VBA]Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Interior.ColorIndex = 49
Selection.Font.ColorIndex = 2

Selection.Offset(2, 0).Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With[/VBA]

[Paul_Hossler]

[VBA]
Option Explicit
Sub EveryOther()
Dim i As Long
With ActiveSheet.Cells(1, 1).CurrentRegion
With .Rows(1)
.Interior.ColorIndex = 49
.Font.ColorIndex = 2
End With

For i = 3 To .Rows.Count Step 2
With .Rows(i).Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Next i
End With
End Sub
[/VBA]

Paul

[mbarron]

One way:
[VBA]Sub alt()
Dim i As Long, lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
If lRow Mod 2 = 0 Then lRow = lRow - 1
For i = lRow To 3 Step -2
With Range("a" & i & ":C" & i).Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Next
End Sub[/VBA]

[mdmackillop]

another:
[VBA]
Sub grey()
Dim Rw As Range
For Each Rw In ActiveSheet.UsedRange.Rows
If Rw.Row > 2 And Rw.Row Mod 2 = 1 Then Rw(1).Resize(, 3).Interior.ColorIndex = 15
Next
End Sub

[/VBA]

[lukecj]

Thanks for the responses!

[Paul_Hossler]

Note that .UsedRange might include currently empty rows below your block of data sometimes, usually if there was data there and you didn't specifically delete the now empty rows, or if you did not reset the last used row in VBA.

Paul