VBA to intersect cell changes (both data validation) and action separate functions

[yhtomitl]

Hi Everyone, on my worksheet I have two cells B3 and C3 which have data validation. So what I want to do is when either of the cells change, it will update two pivot table's filters. (Target & Actual)

Below is what I got working when looking at only B3 and updating the Region. The values within "Region Group" & "Region2" are the same, likewise "D Zone" & "Zone".

'Private Sub Worksheet_Change(ByVal Target As Range)
'
'    Dim PT As PivotTable
'    Dim PF As PivotField
'    Dim PT2 As PivotTable
'    Dim PF2 As PivotField
'    Dim Str As String
'    On Error Resume Next
'
'    If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
'    Application.ScreenUpdating = False
'
'    Set PT = Worksheets("RetailsSPLive").PivotTables("Ret_Region")
'    Set PF = PT.PivotFields("Region Group")
'    Set PT2 = Worksheets("DB Target").PivotTables("DB_Target")
'    Set PF2 = PT2.PivotFields("Region2")
'    Str = Target.Text
'    PF.ClearAllFilters
'    PF.CurrentPage = Str
'    PF2.ClearAllFilters
'    PF2.CurrentPage = Str
'    Application.ScreenUpdating = True
'
'End Sub

However when I continue building upon it to also intersect C3, now neither cell change are being recognised.

    Dim PT As PivotTable
    Dim PF As PivotField
    Dim PT2 As PivotTable
    Dim PF2 As PivotField
    Dim Str As String
    On Error Resume Next
    
    If Intersect(Target, Range("C3")) Then 'Is Nothing Then
    Application.ScreenUpdating = False


    Set PT = Worksheets("RetailsSPLive").PivotTables("Ret_D")
    Set PF = PT.PivotFields("D Zone")
    Set PT2 = Worksheets("DB Target").PivotTables("DB_Target")
    Set PF2 = PT2.PivotFields("Zone")


        If Target.Text = "(All)" Then
        Str = Target.Text
        PF.ClearAllFilters
        PF2.ClearAllFilters
        Application.ScreenUpdating = True


        Else
        Str = Target.Text
        PF.ClearAllFilters
        PF.CurrentPage = Str
        PF2.ClearAllFilters
        PF2.CurrentPage = Str
        Application.ScreenUpdating = True
        End If




    ElseIf Intersect(Target, Range("B3")) Then 'Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    
    Set PT = Worksheets("RetailsSPLive").PivotTables("Ret_Region")
    Set PF = PT.PivotFields("Region Group")
    Set PT2 = Worksheets("DB Target").PivotTables("DB_Target")
    Set PF2 = PT2.PivotFields("Region2")
    Str = Target.Text
    PF.ClearAllFilters
    PF.CurrentPage = Str
    PF2.ClearAllFilters
    PF2.CurrentPage = Str
    Application.ScreenUpdating = True


    Else: Exit Sub
    End If
    
End Sub

 

[Fluff]

It should be like

   If Not Intersect(Target, Range("C3")) Is Nothing Then

and the same for the elseif

 

[yhtomitl]

Hi Fluff,

Thank you! It's working now.

Still trying to get my head around why it's if not... is nothing

 

[Fluff]

It's VBA's convoluted way of saying if it exists. You cannot test to see if the range exists, so you have to test if it doesn't.
in English rather than saying "If not x is nothing" we'd say "If x is not nothing"

As both those lines are checking a single cell rather than a range of cells you could also use

If Target.Address="$C$3" Then