PDA

View Full Version : Solved: Every other line



lukecj
03-23-2010, 07:32 AM
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.

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

Paul_Hossler
03-23-2010, 08:00 AM
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


Paul

mbarron
03-23-2010, 08:02 AM
One way:
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

mdmackillop
03-23-2010, 10:49 AM
another:

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

lukecj
03-23-2010, 10:59 AM
Thanks for the responses!

Paul_Hossler
03-24-2010, 08:43 AM
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