How to use multiple ranges with overlapping cells in same Sub

E

Excel Jr

New Member
Joined
Aug 17, 2018
Messages
26
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.
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:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
You need to be careful when updating values in a "Worksheet_Change" macro!
They run when a cell is updated. So if your code, itself, updates cells, it calls itself, and you can easily end up in an endless loop!
So you often need to disable events temporarily while making your cell updates to prevent the code from calling itself.

Also, "Target" is already a range (it is the cell/range that was just updated).
So this:
Code: 
Range(Target.Address)
is unnecessarily redundant. It is the same thing as "Target".

Try this and see if it works:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim TriggerA As Range
Dim TriggerB As Range
Dim TriggerC As Range
Dim A As Range
Dim B As Range
Dim C As Range

Set TriggerA = Range("B5")
Set TriggerB = Range("B7")
Set TriggerC = Range("B9")

Application.EnableEvents = False

'1st Trigger
If Not Intersect(TriggerA, Target) Is Nothing Then
    Set A = Sheets("Inputs").Range("B7:B85,D13:D87").SpecialCells(xlCellTypeConstants)
    A.ClearContents
End If

'2nd Trigger
If Not Intersect(TriggerB, Target) Is Nothing Then
    Set B = Sheets("Inputs").Range("B9:B85,D13:D87").SpecialCells(xlCellTypeConstants)
    B.ClearContents
End If

'3rd Trigger
If Not Intersect(TriggerC, Target) Is Nothing Then
    Set C = Sheets("Inputs").Range("B81,B83,B85").SpecialCells(xlCellTypeConstants)
    C.ClearContents
End If

Application.EnableEvents = True

End Sub
Also, be aware that "Worksheet_Change" only is triggered on cells that are manually updated (and not cells who values change via a formula).
 
Upvote 0
Sorry, I had the events disabled but cut them from the code I pasted here as it's over a few pages after all the formulas and conditional formatting is reapplied each time. I tried changing how I address the target range and still got the same error :(
 
Upvote 0
I keep getting the area when naming the second range:
Code: 
Set B = Sheets("Inputs").Range("B9:B85,D13:D87").SpecialCells(xlCellTypeConstants)
 
Last edited:
Upvote 0
I keep getting the area when naming the second range:
Click to expand...
But the first one works okay?

Do you have any merged cells or protected cells in this second range?
 
Upvote 0
Correct and I'm sure you figured it out, but I meant error (not area lol). No merged cells, but there are locked cells, however those shouldn't be an issue because I unlock the worksheet at the start and lock it back at the end with code
Code: 
Sheets("Inputs").Unprotect
Code: 
Sheets("Inputs").Protect
 
Last edited:
Upvote 0
however those shouldn't be an issue because I unlock the worksheet at the start and lock it back at the end with code
Click to expand...
Where exactly do you do this?
In this procedure, or in a different one?

If you test making a change to your first trigger, does that work?

Either way, it worked for me when I tested it out. So I don't think there is any issue inherent to the code.
It probably is something with the sheet/data/protection.
 
Last edited:
Upvote 0
Also, I see your code is referencing the "Inputs" sheet.
Is that the name of the sheet that this Worksheet_Change code is in?
 
Upvote 0
Yes and Yes, here's the full code
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim TriggerA As Range
Dim TriggerB As Range
Dim TriggerC As Range
Dim A As Range
Dim B As Range
Dim C As Range

Set TriggerA = Range("B5")
Set TriggerB = Range("B7")
Set TriggerC = Range("B9")

Application.EnableEvents = False
    
'1st Trigger
If Not Application.Intersect(TriggerA, Target) Is Nothing Then

    'Unlocks sheet so macro can make changes
    Sheets("Inputs").Unprotect

    Set A = Sheets("Inputs").Range("B7:B85,D13:D87").SpecialCells(xlCellTypeConstants)

    'Resets entire range
    A.ClearContents

    'Fills the formulas back in
    Sheets("Inputs").Range("B7").Value = "--Select--"
    Sheets("Inputs").Range("B9").Formula = "=IF(OR(V1=W1,V1=W2,V1=W3,V1=W4),""--Select Type (If Sales)--"","""")"
    Sheets("Inputs").Range("B13,B15,B17,B19,B21,B23,B25,B27,B29,B31,B33,B35,B37,B39,B41,B43,B45,B47,B49,B51,B53,B55,B57,B59,B61,B63,B65,B67,B69,B71,B73,B75,B77,B79").Formula = "=IF(C:C<>"""",""--Select--"","""")"
    Sheets("Inputs").Range("B81").Formula = "=IFERROR(IF(OR(V7=W7,V7=W9,V7=W10,V7=W11,V7=W13,V7=W15,V7=W17,V7=W19),VLOOKUP(B83,LoadCrossReferences!$E$2:$F$87,2,FALSE),IF(OR(V7=W23,V7=W25,V7=W27,V7=W29),""--Select--"","""")),"""")"
    Sheets("Inputs").Range("B83").Formula = "=IFERROR(IF(OR(V7=W7,V7=W9,V7=W10,V7=W11),VLOOKUP(B85,LoadCrossReferences!$D$2:$E$87,2,FALSE),IF(OR(V7=W13,V7=W15,V7=W17,V7=W19),""--Select--"","""")),"""")"
    Sheets("Inputs").Range("B85").Formula = "=IF(C:C<>"""",""--Select--"","""")"
    Sheets("Inputs").Range("D13,D15,D17,D19,D21,D23,D25,D27,D29,D31,D33,D35,D37,D39,D41,D43,D45,D47,D49,D51,D53,D55,D57,D59,D61,D63,D65,D67,D69,D71,D73,D75,D77,D79").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3],LoadAnswerFields!C[1]:C[2],2,FALSE),"""")"
    Sheets("Inputs").Range("D87").Formula = "=IF(A87=""Miscellaneous Information"", ""Enter Miscellaneous Information Here"","""")"

    'Resets the conditional formating
    Range("B7:B85").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlNoBlanksCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.NumberFormat = "General"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
    End With

    Selection.FormatConditions(1).StopIfTrue = False

    Range("D13:D87").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlNoBlanksCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.NumberFormat = "General"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
    End With

    Selection.FormatConditions(1).StopIfTrue = False

    'Returns cursor to start of form
    Range("B5").Select

    'Locks sheet back for the user
    Sheets("Inputs").Protect

End If

'2nd Trigger
If Not Application.Intersect(TriggerB, Target) Is Nothing Then

    'Unlocks sheet so macro can make changes
    Sheets("Inputs").Unprotect

    Set B = Sheets("Inputs").Range("B9:B85,D13:D87").SpecialCells(xlCellTypeConstants)

    'Resets entire range
    B.ClearContents

    'Fills the formulas back in
    Sheets("Inputs").Range("B9").Formula = "=IF(OR(V1=W1,V1=W2,V1=W3,V1=W4),""--Select Type (If Sales)--"","""")"
    Sheets("Inputs").Range("B13,B15,B17,B19,B21,B23,B25,B27,B29,B31,B33,B35,B37,B39,B41,B43,B45,B47,B49,B51,B53,B55,B57,B59,B61,B63,B65,B67,B69,B71,B73,B75,B77,B79").Formula = "=IF(C:C<>"""",""--Select--"","""")"
    Sheets("Inputs").Range("B81").Formula = "=IFERROR(IF(OR(V7=W7,V7=W9,V7=W10,V7=W11,V7=W13,V7=W15,V7=W17,V7=W19),VLOOKUP(B83,LoadCrossReferences!$E$2:$F$87,2,FALSE),IF(OR(V7=W23,V7=W25,V7=W27,V7=W29),""--Select--"","""")),"""")"
    Sheets("Inputs").Range("B83").Formula = "=IFERROR(IF(OR(V7=W7,V7=W9,V7=W10,V7=W11),VLOOKUP(B85,LoadCrossReferences!$D$2:$E$87,2,FALSE),IF(OR(V7=W13,V7=W15,V7=W17,V7=W19),""--Select--"","""")),"""")"
    Sheets("Inputs").Range("B85").Formula = "=IF(C:C<>"""",""--Select--"","""")"
    Sheets("Inputs").Range("D13,D15,D17,D19,D21,D23,D25,D27,D29,D31,D33,D35,D37,D39,D41,D43,D45,D47,D49,D51,D53,D55,D57,D59,D61,D63,D65,D67,D69,D71,D73,D75,D77,D79").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3],LoadAnswerFields!C[1]:C[2],2,FALSE),"""")"
    Sheets("Inputs").Range("D87").Formula = "=IF(A87=""Miscellaneous Information"", ""Enter Miscellaneous Information Here"","""")"

    'Resets the conditional formating
    Range("B9:B85").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlNoBlanksCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.NumberFormat = "General"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
    End With

    Selection.FormatConditions(1).StopIfTrue = False

    Range("D13:D87").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlNoBlanksCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.NumberFormat = "General"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
    End With

    Selection.FormatConditions(1).StopIfTrue = False

    'Returns cursor to start of form
    Range("B7").Select

    'Locks sheet back for the user
    Sheets("Inputs").Protect

End If

'3rd Trigger
If Not Application.Intersect(TriggerC, Target) Is Nothing Then

    'Unlocks sheet so macro can make changes
    Sheets("Inputs").Unprotect

    Set C = Sheets("Inputs").Range("B81,B83,B85").SpecialCells(xlCellTypeConstants)

    'Resets entire range
    C.ClearContents

    'Fills the formulas back in
    Sheets("Inputs").Range("B81").Formula = "=IFERROR(IF(OR(V7=W7,V7=W9,V7=W10,V7=W11,V7=W13,V7=W15,V7=W17,V7=W19),VLOOKUP(B83,LoadCrossReferences!$E$2:$F$87,2,FALSE),IF(OR(V7=W23,V7=W25,V7=W27,V7=W29),""--Select--"","""")),"""")"
    Sheets("Inputs").Range("B83").Formula = "=IFERROR(IF(OR(V7=W7,V7=W9,V7=W10,V7=W11),VLOOKUP(B85,LoadCrossReferences!$D$2:$E$87,2,FALSE),IF(OR(V7=W13,V7=W15,V7=W17,V7=W19),""--Select--"","""")),"""")"
    Sheets("Inputs").Range("B85").Formula = "=IF(C:C<>"""",""--Select--"","""")"

    'Resets the conditional formating
    Range("B81:B85").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlNoBlanksCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.NumberFormat = "General"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
    End With

    Selection.FormatConditions(1).StopIfTrue = False

'Returns cursor to start of form
    Range("B9").Select

'Locks sheet back for the user
    Sheets("Inputs").Protect

End If

Application.EnableEvents = True

End Sub

Thanks for sticking through this, the help is greatly appreciated!
 
Last edited:
Upvote 0
What happens if you run this code by itself?
Does it work?
Code: 
Sub Test()

    Dim A as Range

    Sheets("Inputs").Unprotect

    Set A = Sheets("Inputs").Range("B7:B85,D13:D87").SpecialCells(xlCellTypeConstants)
    MsgBox A.Address

    Sheets("Inputs").Protect

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Multiple conditional formatting
Replies
6
Views
311
StephenCrump
S
S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D
C
  • Question Question
Ton of script in case anyone wants to review, maybe something useful to someone
Replies
0
Views
3K
cizzett
C
D
  • Question Question
VBA inserting copied cells using inputbox
Replies
7
Views
711
dannyh
D
J
  • Solved
Macro que controla cambios en varias celdas de un rango / Macro that handles changes in multiple cells in a range
Replies
3
Views
4K
Fluff
Fluff

Forum statistics

Threads
1,224,876
Messages
6,181,523
Members
453,052
Latest member
patriciagarcia3w

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top