Simon Lloyd
04-25-2008, 01:12 AM
Hi all, just on my way to bed but a 4am this morning i had a little trouble with this:
For iRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row To 2 Step -2
With Sheets("Sheet2")
.Rows(iRow).Range(.Cells(iRow, 1), .Cells(iRow, Columns.Count).End(xlToLeft)).Interior.ColorIndex = 3
End With
Next iRow
it works in that it will colour every second row in amount specified, however i thought that:
.Rows(iRow).Range(.Cells(iRow, 1), .Cells(iRow, Columns.Count).End(xlToLeft))
would only allow the used range of each row i want formatted to be coloured, but it actually colours from the first cell to the last used column for the whole block!, so i tried a variation:
For iRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row To 2 Step -2
With Sheets("Sheet2")
MsgBox .Rows(iRow).Range(.Cells(1, iRow), .Cells(iRow, Columns.Count).End(xlToLeft)).Address
End With
Next iRow
but gave very odd results it gave a message showing every 4th row but for double the size of the range!, i am now trying to get this to work:
For iRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row To 2 Step -2
With Sheets("Sheet2")
MsgBox .Rows(iRow).Range(.Cells(1, iRow), .Cells(.Columns.Count.End(xlToLeft).Column, iRow)).Address
End With
Next iRow
but cant get the syntax right, maybe its because i'm bog eyed at the moment so a shove in the right direction would be nice!
For iRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row To 2 Step -2
With Sheets("Sheet2")
.Rows(iRow).Range(.Cells(iRow, 1), .Cells(iRow, Columns.Count).End(xlToLeft)).Interior.ColorIndex = 3
End With
Next iRow
it works in that it will colour every second row in amount specified, however i thought that:
.Rows(iRow).Range(.Cells(iRow, 1), .Cells(iRow, Columns.Count).End(xlToLeft))
would only allow the used range of each row i want formatted to be coloured, but it actually colours from the first cell to the last used column for the whole block!, so i tried a variation:
For iRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row To 2 Step -2
With Sheets("Sheet2")
MsgBox .Rows(iRow).Range(.Cells(1, iRow), .Cells(iRow, Columns.Count).End(xlToLeft)).Address
End With
Next iRow
but gave very odd results it gave a message showing every 4th row but for double the size of the range!, i am now trying to get this to work:
For iRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row To 2 Step -2
With Sheets("Sheet2")
MsgBox .Rows(iRow).Range(.Cells(1, iRow), .Cells(.Columns.Count.End(xlToLeft).Column, iRow)).Address
End With
Next iRow
but cant get the syntax right, maybe its because i'm bog eyed at the moment so a shove in the right direction would be nice!