How about:

Sub ColorEverySixthRow()
' Dimension variables

Dim lastCOL As Integer
Dim lastROW As Long
Dim Counter As Long

'Initiate variables

lastCOL = Cells(2, Columns.Count).End(xlToLeft).Column
lastROW = Cells(Rows.Count, 1).End(xlUp).Row

For Counter = 2 To lastROW Step 6

Range(Cells(Counter, 1), Cells(Counter, lastCOL)). _
Interior.ColorIndex = 15
Next

End Sub

Hope this helps
Rowan

"David O. Antillon" wrote:

Hi,

I have created a table in Excel. To make it easier to read, I need something that changes the cell color every sixth row from column A to J. I want the VBA subroutine to find the last column in a row that has data. What I have below works. However, I want to subroutine to skip to every sixth row, starting with cell A2 until the last row with data. What I have below works, but I dont want to hard code the 272 (the last row on this particular table). I want the subroutine to continue to that row and stop. I have tried several suggestions made to the newsgroup, but the routines stop at 266 or goes on an infinite loop. Can you recommend some changes?









Private Sub ColorEverySixthRow()



ActiveSheet.Range("A2").Select



' Dimension variables

Dim lastCOL As Long

Dim lastROW As Long



'Initiate variables

lastROW = 2

lastCOL = Sheets("Sheet1").Range("IV1").End(xlToLeft).Offset (0, 1).Column



'Do Until lastROW = 272



For lastROW = 2 To 272 Step 6

With ActiveSheet

.Range(.Cells(lastROW, 1), .Cells(lastROW, lastCOL - 1)).Interior.ColorIndex = 15

End With

Next



End Sub





I have xl2000 on a windows 98 box.



Thanks.



danz98