Please explain this code to me

[klandreth]

Hi! Learning more and more about VBA everyday! :cloud9: But I don't understand this line of code (written by my predecessor):

Dim lRow as long, rCell as Range
.
.
.
For each rCell in Intersect(Target, Columns("O"))
.
.
.
lRow = rCell.row
.
.
.
range("O2:O2").offset(lRow - 2).interior.color = vbRed
.
.
.
.
next RCell


I get what he's trying to do .... turn the cell RED, but I don't understand the "offset(lRow - 2)" :banghead:

Isn't there a clearer way of specifying this? (Note: the worksheet has two header rows that are ignored throughout processing by starting at either row 3 or subtracting 2 from the total number of rows/last row position.)

Thanks for the service you provide.:beerchug:

[Bob Phillips]

It offsets from O2 row by the target row less 2, which is effectively just getting the current cell row, so is totally pointless as rCell tells you that.

You could use

rCell.Interior.Color = vbRed

[klandreth]

Thank you. I thought as much (rcell.interior.color) but thought maybe I was missing something.

[Bob Phillips]

Not Target, rCell, as more than 1 cell could be selected so Target could be multi-cell.

[klandreth]

yes, yes. The first time I typed my response it was from my Android phone. I realized my error, and went back & fixed it. However, I have another question.

In the same code, it references a couple cells outside the Intersect range:

sCellVal = range("N2:N2").offset(lRow - 2).Value
.
.
.
.
.
range("au2:au2").offset(lRow - 2).Value = sErrMsg
.
.
.

Is there a better way of referencing these cells?

[Bob Phillips]

If the Intersect range is always column O, you could use

rng.Offset(0, -1)

and

rng.Offset(0, 32)


where rng is your cell in column O