Application.Intersect Range targeting Another Sheet's Cell Value

[Rafiello13579]

I have a VBA code for hiding/unhiding rows depending on the value of a cell within the sheet where the rows are hidden/unhidden. Below is the code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("B4"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value

Case Is = "": Rows("17:31").EntireRow.Hidden = True
Case Is = 0: Rows("17:31").EntireRow.Hidden = True
Case Is = 1: Rows("18:31").EntireRow.Hidden = True
Rows("17").EntireRow.Hidden = False
Case Is = 2: Rows("19:31").EntireRow.Hidden = True
Rows("17:18").EntireRow.Hidden = False
Case Is = 3: Rows("20:31").EntireRow.Hidden = True
Rows("17:19").EntireRow.Hidden = False
Case Is = 4: Rows("21:31").EntireRow.Hidden = True
Rows("17:20").EntireRow.Hidden = False
Case Is = 5: Rows("22:31").EntireRow.Hidden = True
Rows("17:21").EntireRow.Hidden = False
Case Is = 6: Rows("23:31").EntireRow.Hidden = True
Rows("17:22").EntireRow.Hidden = False
Case Is = 7: Rows("24:31").EntireRow.Hidden = True
Rows("17:23").EntireRow.Hidden = False
Case Is = 8: Rows("25:31").EntireRow.Hidden = True
Rows("17:24").EntireRow.Hidden = False
Case Is = 9: Rows("26:31").EntireRow.Hidden = True
Rows("17:25").EntireRow.Hidden = False
Case Is = 10: Rows("27:31").EntireRow.Hidden = True
Rows("17:26").EntireRow.Hidden = False
Case Is = 11: Rows("28:31").EntireRow.Hidden = True
Rows("17:27").EntireRow.Hidden = False
Case Is = 12: Rows("29:31").EntireRow.Hidden = True
Rows("17:28").EntireRow.Hidden = False
Case Is = 13: Rows("30:31").EntireRow.Hidden = True
Rows("17:29").EntireRow.Hidden = False
Case Is = 14: Rows("31").EntireRow.Hidden = True
Rows("17:30").EntireRow.Hidden = False
Case Is = 15: Rows("17:31").EntireRow.Hidden = False

End Select

End If

End Sub

The code works fine as is, but I was wondering if there was a way to change the range below in red to another sheet's cell value:
Application.Intersect(Range("B4"), Range(Target.Address))

The idea is to use a different sheet as a "control panel" to change which rows to hide/unhide as needed. I tried the following methods, but neither worked:
Application.Intersect(Sheets("Function Count").Range("B4"), Range(Target.Address))
Application.Intersect(Range(Sheets("Function Count").Range("B4")), Range(Target.Address))

If this is not possible, it's not the end of the world. I can make it work. It would just be convenient.

 

[Joe4]

Welcome to the Board!

You are coming at this backwards. Worksheet_Change procedures ALWAYS watch cells on the sheet the code is found in.
So you would just insert a Worksheet_Change procedure on the other sheet to watch for changes in cells on that page.
You can then have the code that tells it which rows to hide run on a different sheet.

 

[RoryA]

Also note that Range(Target.Address) is completely unnecessary as Target is already the range you want.

 

[Rafiello13579]

Thank you Joe4 and RoryA for the feedback. I'll admit that my Excel education is not formal, so I appreciate the help. This is also borrowed code from another post, so I will adjust it the next chance I get and reply with my results.

 

[Rafiello13579]

After adjusting the VBA code and moving it to the "control panel" sheet, it works perfectly. This was my resulting code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Range("B4"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
   
    Case Is = "": Sheets("Associate Roster").Rows("17:31").EntireRow.Hidden = True
    Case Is = 0: Sheets("Associate Roster").Rows("17:31").EntireRow.Hidden = True
    Case Is = 1: Sheets("Associate Roster").Rows("18:31").EntireRow.Hidden = True
                    Sheets("Associate Roster").Rows("17").EntireRow.Hidden = False              
    Case Is = 2: Sheets("Associate Roster").Rows("19:31").EntireRow.Hidden = True
                    Sheets("Associate Roster").Rows("17:18").EntireRow.Hidden = False                
    Case Is = 3: Sheets("Associate Roster").Rows("20:31").EntireRow.Hidden = True
                    Sheets("Associate Roster").Rows("17:19").EntireRow.Hidden = False               
    Case Is = 4: Sheets("Associate Roster").Rows("21:31").EntireRow.Hidden = True
                    Sheets("Associate Roster").Rows("17:20").EntireRow.Hidden = False              
    Case Is = 5: Sheets("Associate Roster").Rows("22:31").EntireRow.Hidden = True
                    Sheets("Associate Roster").Rows("17:21").EntireRow.Hidden = False                
    Case Is = 6: Sheets("Associate Roster").Rows("23:31").EntireRow.Hidden = True
                    Sheets("Associate Roster").Rows("17:22").EntireRow.Hidden = False   
    Case Is = 7: Sheets("Associate Roster").Rows("24:31").EntireRow.Hidden = True
                    Sheets("Associate Roster").Rows("17:23").EntireRow.Hidden = False              
    Case Is = 8: Sheets("Associate Roster").Rows("25:31").EntireRow.Hidden = True
                    Sheets("Associate Roster").Rows("17:24").EntireRow.Hidden = False               
    Case Is = 9: Sheets("Associate Roster").Rows("26:31").EntireRow.Hidden = True
                    Sheets("Associate Roster").Rows("17:25").EntireRow.Hidden = False
    Case Is = 10: Sheets("Associate Roster").Rows("27:31").EntireRow.Hidden = True
                    Sheets("Associate Roster").Rows("17:26").EntireRow.Hidden = False               
    Case Is = 11: Sheets("Associate Roster").Rows("28:31").EntireRow.Hidden = True
                    Sheets("Associate Roster").Rows("17:27").EntireRow.Hidden = False                
    Case Is = 12: Sheets("Associate Roster").Rows("29:31").EntireRow.Hidden = True
                    Sheets("Associate Roster").Rows("17:28").EntireRow.Hidden = False     
    Case Is = 13: Sheets("Associate Roster").Rows("30:31").EntireRow.Hidden = True
                    Sheets("Associate Roster").Rows("17:29").EntireRow.Hidden = False               
    Case Is = 14: Sheets("Associate Roster").Rows("31").EntireRow.Hidden = True
                    Sheets("Associate Roster").Rows("17:30").EntireRow.Hidden = False                
    Case Is = 15: Sheets("Associate Roster").Rows("17:31").EntireRow.Hidden = False
              
        End Select
   
    End If

End Sub

I tried removing/altering the Range(Target.Address) portion of the code, but I ran in others in doing so. I just left it in since it wasn't hurting the code having it. Thanks again for the assistance!

 

[Joe4]

Note that as Rory said:

Range(Target.Address)

is exactly the same as

Target

It is sort of like adding one to a value, and then immediately subtracting one.
You end up exactly where you started!