Two Conditions for Hiding and Unhiding Cells

[legolas97]

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("B11"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
        Case Is = "Yes/No": Rows("12:19").EntireRow.Hidden = False
        Case Is = "Yes"
        Rows("12").EntireRow.Hidden = False
        Rows("13").EntireRow.Hidden = True
        Rows("17").EntireRow.Hidden = True
        Rows("19").EntireRow.Hidden = True
        Rows("16").EntireRow.Hidden = False
        Rows("18").EntireRow.Hidden = False
                            
        Case Is = "No"
        Rows("12").EntireRow.Hidden = True
        Rows("13").EntireRow.Hidden = False
        Rows("14:15").EntireRow.Hidden = False
        Rows("16").EntireRow.Hidden = True
        Rows("18").EntireRow.Hidden = True
        Rows("17").EntireRow.Hidden = False
        Rows("19").EntireRow.Hidden = False
                            
    End Select

End If
        
If Not Application.Intersect(Range("B14"), Range(Target.Address)) Is Nothing Then
    
    Select Case Target.Value
    

    
    Case Is = "No"
    Rows("15:19").EntireRow.Hidden = True
    Rows("21").EntireRow.Hidden = False
        
        
    End Select



End If

If Target.Address(0, 0) = "B15" Then

    Select Case Target.Value
    
    
    Case Is = "Yes"
    Rows("17").EntireRow.Hidden = False
    Rows("19").EntireRow.Hidden = True
    
    Case Is = "No"
      Rows("17").EntireRow.Hidden = True
      Rows("19").EntireRow.Hidden = False
    
    
    End Select



End If



End Sub

1)I need row 16 to be visible and 17,18,19 to be hidden if B11 and B15 are yes.
2)I need row 19 to be visible and 16,17,18 to be hidden if B11 and B15 are no.

and

3)I need row 17 to be visible, 16,18,19 to be hidden if B11 is yes and B15 is no.
4)I need row 18 to be visible, 16,17,19 to be hidden if B11 is no and B15 is yes.


I'm able to make 2 and 4 happen. However, I'm not able to make 1 and 3 happen as you can see above ^.

 

[Georgiboy]

You could combine the two criteria and look at them together with logic like the below:

Sub test()
    Dim CellOne As String, CellTwo As String
    
    CellOne = Range("B11").Value
    CellTwo = Range("B15").Value
    
    Cells.EntireRow.Hidden = False
    
    Select Case UCase(CellOne & CellTwo)
        Case "YESYES"
            Rows(16).EntireRow.Hidden = False
            Rows("17:19").EntireRow.Hidden = True
        Case "NONO"
            Rows(19).EntireRow.Hidden = False
            Rows("16:18").EntireRow.Hidden = True
        Case "YESNO"
            Rows(17).EntireRow.Hidden = False
            Rows(16).EntireRow.Hidden = True
            Rows("18:19").EntireRow.Hidden = True
        Case "NOYES"
            Rows(18).EntireRow.Hidden = False
            Rows(19).EntireRow.Hidden = True
            Rows("16:17").EntireRow.Hidden = True
    End Select
End Sub

 

[legolas97]

You could combine the two criteria and look at them together with logic like the below:

Sub test()
    Dim CellOne As String, CellTwo As String
   
    CellOne = Range("B11").Value
    CellTwo = Range("B15").Value
   
    Cells.EntireRow.Hidden = False
   
    Select Case UCase(CellOne & CellTwo)
        Case "YESYES"
            Rows(16).EntireRow.Hidden = False
            Rows("17:19").EntireRow.Hidden = True
        Case "NONO"
            Rows(19).EntireRow.Hidden = False
            Rows("16:18").EntireRow.Hidden = True
        Case "YESNO"
            Rows(17).EntireRow.Hidden = False
            Rows(16).EntireRow.Hidden = True
            Rows("18:19").EntireRow.Hidden = True
        Case "NOYES"
            Rows(18).EntireRow.Hidden = False
            Rows(19).EntireRow.Hidden = True
            Rows("16:17").EntireRow.Hidden = True
    End Select
End Sub

Hey! Just gave this a try, maybe I did it wrong, but its not hiding or unhiding anything mate!

 

[Georgiboy]

You have to run the above, i took it away from the worksheet change to show you the logic of joining the two strings together.

If you place that code in a standard sub and run it from there it will do it's thing, it was created to show the logic on how i may deal with the issue.