CODEWITHSUNDEEP
excelvba
Basics B
Basics B

Reputation: 25

Need to unhide a row if 5 cells values change to a certain value

I'm creating a questionnaire where certain questions hide or show depending on the responses.

I've managed this for individual cell changes.

The problem I am having is that if 5 questions are responded to with value "No" then a certain row un-hides.

So if cells K20,K22,K24, K30 and K32 all change to "No" then unhide row 34

I've tried creating a formula cell that reads all the values and outputs "Yes" if the 5 questions are "No" but I think the cell value is not changing as its a formula not a value.

Any help would be greatly appreciated as I'm new to VBA and against the clock.

Kind regards, :)

Here's the code.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(False, False) = "S32" And Target.Value = "Yes" Then
      Call PQShowQ7
       Else
End If

End Sub

Which activates this Module

Sub PQShowQ7()

With Sheets("Initial Scoping - WIP")

    Sheets("Initial Scoping - WIP").Unprotect ("xxx")

        .Range("A34", "A35").EntireRow.Hidden = False

    Sheets("Initial Scoping - WIP").Protect ("xxx")

        .Range("J34").Value = "Please Select:"

    End With

    Sheets("Initial Scoping - WIP").Protect ("xxx")

End Sub

Upvotes: 0

Views: 61

Answers (1)

Mikku
Mikku

Reputation: 6664

Instead of Doing a Formula and checking that, You can change it to

Private Sub Worksheet_Change(ByVal Target As Range)


If Range("K20") = "No" And Range("K22") = "No" And Range("K24") = "No" And Range("K30") = "No" And Range("K32") = "No" Then

      Call PQShowQ7

End If

End Sub

Upvotes: 1

Related Questions