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
Bookmarks