Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when Target Range is block, not cell in Worksheet_Change Eve
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when Target Range is block, not cell in Worksheet_Change Eve
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 "Jim Zeeb" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when Target Range is block, not cell in Worksheet_Change
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 "NickHK" wrote: 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 "Jim Zeeb" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
REPSOT?? Sub Worksheet_Change(ByVal Target As Range) | Excel Discussion (Misc queries) | |||
Prevent error when deleting row within Worksheet_Change target ran | Excel Programming | |||
Private Sub Worksheet_Change(ByVal Target As Range) | Excel Programming | |||
Error with Target.Name.Name in Worksheet_Change event | Excel Programming | |||
Many Sub Worksheet_Change(ByVal Target As Range) In One Worksheet | Excel Programming |