Hope all is well!
Working on a sheet currently with VBA- I'm trying to hide and show cells depending on value selected in a cell. The problem I'm having is when I make a selection in the selected cell to select which region, unless I click "All" before going to a different region, it ignores my command and shows cells not needing to be shown. Is there a way to correct this? Here is what I have:
Working on a sheet currently with VBA- I'm trying to hide and show cells depending on value selected in a cell. The problem I'm having is when I make a selection in the selected cell to select which region, unless I click "All" before going to a different region, it ignores my command and shows cells not needing to be shown. Is there a way to correct this? Here is what I have:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("C3"), Target) Is Nothing Then
Select Case Target.Value
Case "All"
Rows("14:42").EntireRow.Hidden = True
Rows("8:13").EntireRow.Hidden = False
Case "Region 1"
Rows("14:17").EntireRow.Hidden = False
Rows("8:13").EntireRow.Hidden = True
Case "Region 2"
Rows("18:21").EntireRow.Hidden = False
Rows("8:17").EntireRow.Hidden = True
Case "Region 3"
Rows("39:42").EntireRow.Hidden = False
Rows("32:38").EntireRow.Hidden = True
Case "Region 4"
Rows("22:31").EntireRow.Hidden = False
Rows("32:42").EntireRow.Hidden = True
Case "Region 5"
Rows("32:38").EntireRow.Hidden = False
Rows("18:21").EntireRow.Hidden = True
End Select
ElseIf Not Application.Intersect(Range("B52"), Target) Is Nothing Then
Select Case Target.Value
Case "Advanced Stage"
Rows("60:62").EntireRow.Hidden = False
Rows("58:59").EntireRow.Hidden = True
Case "Early Stage"
Rows("58:59").EntireRow.Hidden = False
Rows("60:62").EntireRow.Hidden = True
Case "Total Open Pipeline"
Rows("58:62").EntireRow.Hidden = False
End Select
End If
End Sub
Last edited by a moderator: