Simplify intersects in Worksheet_Change Routine

Paul Sansom

Board Regular
Joined
Jan 28, 2013
Messages
178
Office Version
  1. 2021
  2. 2016
Platform
  1. Windows
Is there a simpler way you achieve this exclusion of cells? (Cell contents vary)
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Intersect(Target, Range("A5:AF180")) Is Nothing Or _
    Not Intersect(Target, Range("U8")) Is Nothing Or _
    Not Intersect(Target, Range("X8")) Is Nothing Or _
    Not Intersect(Target, Range("AA8")) Is Nothing Or _
    Not Intersect(Target, Range("AD8")) Is Nothing Or _
    Not Intersect(Target, Range("U32")) Is Nothing Or _
    Not Intersect(Target, Range("X32")) Is Nothing Or _
    Not Intersect(Target, Range("AA32")) Is Nothing Or _
    Not Intersect(Target, Range("AD32")) Is Nothing Or _
    Not Intersect(Target, Range("U56")) Is Nothing Or _
    Not Intersect(Target, Range("X56")) Is Nothing Or _
    Not Intersect(Target, Range("AA56")) Is Nothing Or _
    Not Intersect(Target, Range("AD56")) Is Nothing Or _
    Not Intersect(Target, Range("U101")) Is Nothing Or _
    Not Intersect(Target, Range("X101")) Is Nothing Or _
    Not Intersect(Target, Range("AA101")) Is Nothing Or _
    Not Intersect(Target, Range("AD101")) Is Nothing Or _
    Not Intersect(Target, Range("U129")) Is Nothing Or _
    Not Intersect(Target, Range("X129")) Is Nothing Or _
    Not Intersect(Target, Range("AA129")) Is Nothing Or _
    Not Intersect(Target, Range("AD129")) Is Nothing _
    Then Exit Sub

Cheers Paul
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I think your logic is wrong there as that wouldn't work for any cells at all. You could use:

Code:
If Intersect(Target, Range("A5:AF180,U8,X8,AA8,AD8,U32,X32,AA32,AD32,U56,X56,AA56,AD56,U101,X101,AA101,AD101,U129,X129,AA129,AD129")) Is Nothing Then Exit Sub
 
Upvote 0
How about like
VBA Code:
    If Not Intersect(Target, Range("A5:AF180")) Is Nothing Then
      Select Case Target.Address(0, 0)
         Case "U8", "X8", "AA8"
            Exit Sub
      End Select
      MsgBox 2
   End If
 
Upvote 0
I think your logic is wrong there as that wouldn't work for any cells at all. You could use:

Code:
If Intersect(Target, Range("A5:AF180,U8,X8,AA8,AD8,U32,X32,AA32,AD32,U56,X56,AA56,AD56,U101,X101,AA101,AD101,U129,X129,AA129,AD129")) Is Nothing Then Exit Sub
 
Upvote 0
Hi and Thanks for replys
First line sets the area that triggers a change eg A4 chnage does not but A5 does
VBA Code:
    If Intersect(Target, Range("A5:AF180")) Is Nothing Or _
The remaining lines which i can condence using the example are cells that do not trigger a change
Code:
Not Intersect(Target, Range("U8")) Is Nothing Or _
    Not Intersect(Target, Range("X8")) Is Nothing Or _
    Not Intersect(Target, Range("AA8")) Is Nothing Or _

    etc

So it does work but just very clumsy coding
 
Upvote 0
That is not logical. If you change A5, then you didn't change U8 so that part would be True, and the Or would evaluate to True, and the code would exit. Why are you adding in cells you don't want to trigger the change?
 
Upvote 0
That is not logical. If you change A5, then you didn't change U8 so that part would be True, and the Or would evaluate to True, and the code would exit. Why are you adding in cells you don't want to trigger the change?
Ye you are right but i have another sub that rewrites formula cells if inputs cells to those formula cells contain values. My apologies I neglected to mention that .

VBA Code:
Sub CalucatedValuesInInputs()

    Set Metric = Sheet9.Range("I103")
    For RowVal = 5 To 1001
        If Sheet3.Cells(RowVal, 5) = Metric Then
            For ColVal = 21 To 30 Step 3
                If Sheet3.Cells(RowVal + 1, ColVal) = "" And Sheet3.Cells(RowVal + 2, ColVal) = "" Then
                    Sheet3.Cells(RowVal, ColVal) = Sheet10.Range("$C$78")
                Else
                    Sheet3.Cells(RowVal, ColVal) = Sheet3.Cells(RowVal + 1, ColVal) + Sheet3.Cells(RowVal + 2, ColVal)
                End If
            Next
        End If
    Next

End Sub

This rewites the formulas in those cells when the sheet is deactivated and that is why i needed to exclude them in the _Change sub
 
Upvote 0
If you don't want that to trigger the Change event, just disable events when you run that code.
 
Upvote 0
If you don't want that to trigger the Change event, just disable events when you run that code.
Sometimes the blindingly obvious is not always seen. Many thanks and of course disabling events on that sub call worked and all NOT statements in original removed as unnecessary.
Many thanks, and that why good help is always here if you ask the right question and give all info :)
 
Upvote 0
Sometimes you can't see the forest for the trees when it's your own project! :) Glad we could help.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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