Need help to hide/show excel rows based on value in a cell, but allow for moving rows around later

Question

In Row 4 I ask a question and D4 will contain the answer of either Yes or No.

If the answer is Yes in D4, I want hidden rows 5 and 5 to appear with follow-up questions. This is working for me:

If Range("D4").Value = "No" Then
    Rows("5:6").EntireRow.Hidden = True
ElseIf Range("D4").Value = "Yes" Then
    Rows("5:6").EntireRow.Hidden = False

In Column A I identify the initial questions (like in row 4) as "Tiered Questions" and the subsequent questions (like rows 5 and 6) are labeled "Follow-up Q".

Is there a way to do this without manually updating every row? Also, I need to allow for adding/removing rows above which would impact which rows will hide/unhide. Thanks.

Answer 1

I'm not sure exactly what you mean by "manually updating every row" but if you put your code into a Worksheet_Change sub into your worksheet object (not a module) then it will run automatically every time cell D4 is updated in that worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$4" Then
        If Target.Value = "No" Then
            Rows("5:6").EntireRow.Hidden = True
        ElseIf Target.Value = "Yes" Then
            Rows("5:6").EntireRow.Hidden = False
        End If
    End If
End Sub

Answer 2

I received help from a friend and wanted to share the solution--below.

On Any update to the workbook that happens in column 4 (which is the Client Answer), if they answer No to a "Tiered Question"...the subsequent rows with a "Follow-Up Q" will be hidden.

You would have to update this macro if you moved the Business Criteria ("A") or Client Answer columns.

This should work:

Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer

If Target.Column = 4 Then
    If Range("A" & Target.Row).Text = "Tiered Question" Then
        i = 1
        Do While Range("A" & Target.Row + i).Text = "Follow-Up Q"
            Range("A" & Target.Row + i).EntireRow.Hidden = (Target.Value = "No")
            i = i + 1
        Loop
    End If
End If

End Sub