Macro to Hide Rows based on Cell Value - Not Working

[sguidry]

Hello,

I've written a macro that is supposed to hide/unhide rows based on a cell's value. I want it to run the macro whenever a certain cell is clicked, so the macro re-runs if the value of the specified cell is changed.

When I go to the cell and change the value, nothing happens. The case matches ('Yes', not 'yes'), and the values defined are correct - I am pulling my hair out trying to figure out what's wrong! Any help would be great.

Thank you!

Private Sub Worksheet_ShowYN(ByVal Target As Range)
Application.ScreenUpdating = True
 
'Participate in Program
If Target.Address = ("$B$17") Then
 
If Target.Value = "" Then
Rows("18:19").EntireRow.Hidden = True
End If
 
If Target.Value = "Yes" Then
Rows(18).EntireRow.Hidden = False
Rows(19).EntireRow.Hidden = True
End If
 
If Target.Value = "No" Then
Rows(19).EntireRow.Hidden = False
Rows(18).EntireRow.Hidden = True
End If
End If
End Sub

 

[Jaye7]

Post the following into your worksheet event area.

 Private Sub Worksheet_Change(ByVal Target As Range)
For Each Cell In Range("B17")
        Select Case Cell.Value
            Case vbNullString
                Rows("18:19").EntireRow.Hidden = True
 
Case "Yes", "yes"
Rows(18).EntireRow.Hidden = False
Rows(19).EntireRow.Hidden = True
 
Case "No", "no"
Rows(19).EntireRow.Hidden = False
Rows(18).EntireRow.Hidden = True
  End Select
   Next
    End Sub

 

[sguidry]

Ah, that was it! I had changed the macro name, and I needed to keep it as

Private Sub Worksheet_Change

Thank you!