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 don’t 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