Error when Target Range is block, not cell in Worksheet_Change Eve

[Guest]

I have the following code in the Worksheet_Change event procedure.
I want to mark each row in the target range as changed if any cell in the
row has been changed (Column 20).
It works fine when the target is a single cell. If a block of cells is
pasted into the sheet, I get a 1004 error on trying to set the change flag in
col 20.

Application.EnableEvents=False
For each cel in Target.cells
Set rngTgt = cel.EntireRow.Cells(1, 1)
rngTgt.offset(0,20).value = 1 'Gets 1004 error
next cel

InTHANKSadvance
Jim

 

[NickHK]

Jim,
You mean this ?

Application.EnableEvents = False
Target.Offset(0, 21-Target.Column).Value = 1
Application.EnableEvents = True
You should add a check that Target is in your range of interest.

NickHK

 

[Guest]

Nick,

Thanks for the thought, but your code would not work the way I want it to if
the target was a rectangular set of cells, i.e. C5:F20. I want to update
the same column cell if any other columns on that row were changed.

I want a "1" in cells T5:T20 using the above example. The question is why
does this work with single cell targets and not block targets.

Yes, I already had code to determine the target with within the range I
want, but left it out to keep the exampler simpler.

....jz