VBA code is not working using Private Sub Worksheet_Change Function

September 16, 2022

I am trying to use selection from cell E73 to hide or display rows on two different tabs. my vba code below only works for one tab and not both. Can you spot where my issue is? Any help is appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Target.CountLarge > 1 Then Exit Sub
Select Case Target.Address(0, 0)
    Case "E73"
        Set rng1 = Sheets("Proposal").Rows("349:403")
        Set rng2 = Sheets("Binder").Rows("350:404")
    Case "E128"
        Set rng1 = Sheets("Proposal").Rows("404:462")
        Set rng2 = Sheets("Binder").Rows("405:463")
End Select
If rng Is Nothing Then Exit Sub

    Select Case Target.Value
        Case "Included"
            rng1.Hidden = False
            rng2.Hidden = False
        Case "Excluded"
            rng1.Hidden = True
            rng2.Hidden = True
    End Select
End Sub

>Solution :

If rng Is Nothing Then Exit Sub

This is always True, because rng is never Set.

A guess as to what you want:

Select Case Target.Address(0, 0)
    Case "E73"
        Set rng1 = Sheets("Proposal").Rows("349:403")
        Set rng2 = Sheets("Binder").Rows("350:404")
    Case "E128"
        Set rng1 = Sheets("Proposal").Rows("404:462")
        Set rng2 = Sheets("Binder").Rows("405:463")
    Case Else
        Exit Sub
End Select

Select Case Target.Value
    Case "Included"
        rng1.Hidden = False
        rng2.Hidden = False
    Case "Excluded"
        rng1.Hidden = True
        rng2.Hidden = True
    End Select
End Sub