How to hide rows based on yes/no multiple cell values so that rows don't hide unless all values say no

[annasv]

Hi, I need help with hiding rows based on some yes/no questions. So I have questions and their answers are at cells: B6, B16 and B20, and all of them have an yes/no option and based on their answer rows 28:29 should be hidden, but it should only be hidden if all of the cells say no.

I have been using the following code that works but only if I am referencing one cell but I want to be able to link B16 and B20 as well.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Target.Address = "$B$6" Then
        Select Case Target.Value
       Case Is = "Yes": 
       Rows("28:29").EntireRow.Hidden = True
       Rows("28:29").EntireRow.Hidden = False
                 
        Case Is = "No":
        Rows("28:29").EntireRow.Hidden = False        
        Rows("28:29").EntireRow.Hidden = True
        End Select
      
End If
End Sub

All help would be greatly appreciated

 

[Joe4]

Welcome to the Board!

Please try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Address = "$B$6") Or (Target.Address = "$B$16") Or (Target.Address = "$B$20") Then
    If (Range("B6") = "Yes") Or (Range("B16") = "Yes") Or (Range("B20") = "Yes") Then
        Rows("28:29").EntireRow.Hidden = False
    Else
        Rows("28:29").EntireRow.Hidden = True
    End If
End If

End Sub

Note that there is no need to "activate" the "active sheet". By definition, it is already active!
Also, there is no reason for the duplicate entries for rows 28:29, first hiding to unhide and vice versa.
The second one just overwrites the first. So there is no need for the first.

 

[annasv]

Welcome to the Board!

Please try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Address = "$B$6") Or (Target.Address = "$B$16") Or (Target.Address = "$B$20") Then
    If (Range("B6") = "Yes") Or (Range("B16") = "Yes") Or (Range("B20") = "Yes") Then
        Rows("28:29").EntireRow.Hidden = False
    Else
        Rows("28:29").EntireRow.Hidden = True
    End If
End If

End Sub

Note that there is no need to "activate" the "active sheet". By definition, it is already active!
Also, there is no reason for the duplicate entries for rows 28:29, first hiding to unhide and vice versa.
The second one just overwrites the first. So there is no need for the first.

Thank you!

And yes this worked, thank you so much for your help!

 

[Joe4]

You are welcome.

I hope the logic of the code makes sense.
The first "IF" just checks to see if an update was made to cell B6, B16, or B20.
The second "IF" just checks to see if any of those cells are set to "Yes", and if they are, they it will unhide rows 28:29.
Otherwise (if any of those cells are NOT set to "Yes"), it will hide rows 28:29.