Macro to highlight every 2nd row

[NICK]

Hi,

I am trying to work out how to write a macro so that it
takes my data range and highlights every second row in a
certain colour.

if my data is in the range A2:K31, then i want the macro
to run so it selects A3:K3 and highlights that in yellow,
then skip down two rows and highlights A5:K5 in yellow and
so on until there is no more data in the cells (ie. Row
31).

the code I have so far is this, but it only highlights
every second cell in column A and does NOT extend out to
column K...


Sub NEWY()

Range("A2:K2").Select

Do Until ActiveCell.Value = Empty

With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveCell.Offset(1, 0).Select
Loop

Range("A1").Select

End Sub

 

[DavidP]

Just a small change necessary

Sub NEWY()

Range("A2:K2").Select

Do Until ActiveCell.Value = Empty

With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Range(ActiveCell.Offset(2, 0), ActiveCell.Offset(2, 10)).Select
Loop

Range("A1").Select

End Sub


DavidP

 

[Dave Peterson]

Another option may be to use Format|conditional formatting.

Chip Pearson has a nice explanation at:
http://www.cpearson.com/excel/banding.htm

And you may even find something useable inside:
format|Autoformat