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

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

David,

I am a little confused: first you say columns A:J than you say "last
column" in a row.
But I digress.

I selected a range and put in a Conditional Format.
The formulas are easier to write if you are R1C1 mode (Tools Options)

Formula1 = Row(RC) = 1
No Condition
Formula2 = Row(RC) = 2
Condition
Formula3 = Mod(Row(RC)-1,6) = 0
Condition

formula1 makes sure that row 1 doesn't get changed
formula2 makes sure row 2 gets changed
formula3 changes every 6th row after row2

Try it out. Than do it with the recorder on and you will get the code.
Just edit the code to meet your range requirements.

--
steveB

Remove "AYN" from email to respond
"David O. Antillon" wrote in message
news:eryHe.9499$Bx5.8120@trnddc09...
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

thanks Rowan, your suggestion worked perfectly.

danz98
"Rowan" wrote in message ...
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

J is the last column of the table. I didn't want to hardcode it, since I may use the routine on another table that has more columns. I have been trying out suggestions made by the group and recording with the macro recorder, successfully. On this routine (the one that failed) I crashed my machine twice, so I thought I better ask for help. I'll save your suggestion because I know I'll run into this kind of situation again. Anyway, thanks for your suggestion, and I'll try it next time it comes up.

danz98
"STEVE BELL" wrote in message news:4YzHe.2816$4e6.1901@trnddc04...
David,

I am a little confused: first you say columns A:J than you say "last
column" in a row.
But I digress.

I selected a range and put in a Conditional Format.
The formulas are easier to write if you are R1C1 mode (Tools Options)

Formula1 = Row(RC) = 1
No Condition
Formula2 = Row(RC) = 2
Condition
Formula3 = Mod(Row(RC)-1,6) = 0
Condition

formula1 makes sure that row 1 doesn't get changed
formula2 makes sure row 2 gets changed
formula3 changes every 6th row after row2

Try it out. Than do it with the recorder on and you will get the code.
Just edit the code to meet your range requirements.

--
steveB

Remove "AYN" from email to respond
"David O. Antillon" wrote in message
news:eryHe.9499$Bx5.8120@trnddc09...
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