I haven't been doiung any programming for a while, so maybe I'm a bit rusty,
but why do these two snippets produce two different results?

----DOES WHAT I EXPECT----

r = ActiveCell.Row
Rows(r + 1).EntireRow.Insert
Rows(r + 1).Font.ColorIndex = 3

----COLOR'S ROW 1 FURTHER DOWN----

r = ActiveCell.Row
With Rows(r + 1)
.EntireRow.Insert
.Font.ColorIndex = 3
End With

I dare say the answer's staring me in the face, but I can't see it!

The difference is when Rows(r + 1) is evaluated.

In the first case, Rows(r+1) is evaluated in line 2 and a row inserted.
Then Rows(r+1) is evaluated again to color the cell. So if r = 100, a
row would be inserted at row 101 (pushing row 101 down to row 102) then
row 101 would be formatted.

In the second, Rows(r+1) is only evaluated once. Again assuming r = 100,
..EntireRow.Insert inserts a row at row 101. In the next line however,
the reference will be to the row that was row 101 when the With
statement was executed. Since a row was inserted and row 101 forced down
a row, that reference will now be to row 102.



In article ,
"Ian Chappel" <newsATian-chappel.com wrote:

I haven't been doiung any programming for a while, so maybe I'm a bit rusty,
but why do these two snippets produce two different results?

----DOES WHAT I EXPECT----

r = ActiveCell.Row
Rows(r + 1).EntireRow.Insert
Rows(r + 1).Font.ColorIndex = 3

----COLOR'S ROW 1 FURTHER DOWN----

r = ActiveCell.Row
With Rows(r + 1)
.EntireRow.Insert
.Font.ColorIndex = 3
End With

I dare say the answer's staring me in the face, but I can't see it!

The behaviour does seem quite funny, but if you put line 2 before lin
1, then both the codes work same.

manges

--
Message posted from http://www.ExcelForum.com