Hello everyone. I'm trying to assign a trigger to where if a certain cell is changed then those below it will be cleared as well.
This works fine when I only have one trigger, but trying to add another keeps giving me an error where the range is reset for the 2nd & 3rd triggers
I've tried two different ways and no luck.
1.
2.
The error always occurs when setting the area to clear on the 2nd trigger. I'm guessing because many cells overlap with the clearing area for the 1st trigger. Anyone have any ideas?
Thanks!
This works fine when I only have one trigger, but trying to add another keeps giving me an error where the range is reset for the 2nd & 3rd triggers
I've tried two different ways and no luck.
1.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TriggerA As Range
Dim TriggerB As Range
Dim TriggerC As Range
Set TriggerA = Range("B5")
Set TriggerB = Range("B7")
Set TriggerC = Range("B9")
Dim Area As Range
'1st Trigger
If Not Application.Intersect(TriggerA, Range(Target.Address)) Is Nothing Then
Set Area = Sheets("Inputs").Range("B7:B85,D13:D87").SpecialCells(xlCellTypeConstants)
Area.ClearContents
End If
'2nd Trigger
If Not Application.Intersect(TriggerB, Range(Target.Address)) Is Nothing Then
Set Area = Sheets("Inputs").Range("B9:B85,D13:D87").SpecialCells(xlCellTypeConstants)
Area.ClearContents
End If
'3rd Trigger
If Not Application.Intersect(TriggerC, Range(Target.Address)) Is Nothing Then
Set Area = Sheets("Inputs").Range("B81,B83,B85").SpecialCells(xlCellTypeConstants)
Area.ClearContents
End If
End Sub
2.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TriggerA As Range
Dim TriggerB As Range
Dim TriggerC As Range
Set TriggerA = Range("B5")
Set TriggerB = Range("B7")
Set TriggerC = Range("B9")
'1st Trigger
If Not Application.Intersect(TriggerA, Range(Target.Address)) Is Nothing Then
Dim A As Range
Set A = Sheets("Inputs").Range("B7:B85,D13:D87").SpecialCells(xlCellTypeConstants)
A.ClearContents
End If
'2nd Trigger
If Not Application.Intersect(TriggerB, Range(Target.Address)) Is Nothing Then
Dim B As Range
Set B = Sheets("Inputs").Range("B9:B85,D13:D87").SpecialCells(xlCellTypeConstants)
B.ClearContents
End If
'3rd Trigger
If Not Application.Intersect(TriggerC, Range(Target.Address)) Is Nothing Then
Dim C As Range
Set C = Sheets("Inputs").Range("B81,B83,B85").SpecialCells(xlCellTypeConstants)
C.ClearContents
End If
End Sub
The error always occurs when setting the area to clear on the 2nd trigger. I'm guessing because many cells overlap with the clearing area for the 1st trigger. Anyone have any ideas?
Thanks!
Last edited: