How to use multiple ranges with overlapping cells in same Sub

[Excel Jr]

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.

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.

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!

 

[Excel Jr]

$B$7

I'm not saying I'm correct, but I use the protect/unprotect this way all of the time and have never had any issues with it. Is there maybe a different data type I should be using or an array to store the multiple ranges?

 

[Joe4]

This is one of things that can be hard for us to debug without having access to your file.

The fact that you are not getting any error seems to suggest that the range is OK.

Here is how I usually try to fix an issue like this.
Start off with just your first trigger, and confirm that is working correctly.
If so, then begin to add in your second trigger, bit-by-bit, and test it out to try to find out what part is causing the issue. It isn't always the place being highlighted. It is often something else that is causing it to happen.

I don't think there is anything wrong with the unprotect code, but one thing you can try to confirm that is to remove the protection altogether, and comment out the protect/unprotect lines in your code. Then try running your code and see if that works. If it does, then that suggests the issue is with the protect/unprotect.