Application.Intersect Range targeting Another Sheet's Cell Value

Rafiello13579

New Member
Joined
Mar 3, 2025
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 
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.
 
Upvote 0
Also note that Range(Target.Address) is completely unnecessary as Target is already the range you want.
 
Upvote 0
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.
 
Upvote 0

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