Hide/Unhide rows with CASE based on cell value with formula

[jvandeliefvoort]

Hi all,

I am quite new to VBA, and I cannot find an answer to my problem. I am using code to hide/unhide rows. It works when I put a value in cel B3, but this value must be a value on another sheet. So B3:

='Main Sheet 2.0'!H21

. So it only works if I put for example just the value 7 in B3, but not when I use the formula.

This is the code I used:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = ("$B$3") Then
        Select Case Target.Value
        Case "1"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:13").EntireRow.Hidden = False
        Case "2"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:22").EntireRow.Hidden = False
        Case "3"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:31").EntireRow.Hidden = False
        Case "4"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:40").EntireRow.Hidden = False
        Case "5"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:49").EntireRow.Hidden = False
        Case "6"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:58").EntireRow.Hidden = False
        Case "7"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:67").EntireRow.Hidden = False
        Case "8"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:76").EntireRow.Hidden = False
        Case "9"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:85").EntireRow.Hidden = False
        Case "10"
            Rows("7:94").EntireRow.Hidden = True
            Rows("7:94").EntireRow.Hidden = False
End Select
End If
End Sub

Thank you in advance!

 

[johnnyL]

When I said "it" I was referring to using the formula approach.

 

[jvandeliefvoort]

You need to put the code I suggested after the End Select, not before it.

This works, thanks a lot!!

 

[Fluff]

Glad we could help & thanks for the feedback.