Locking or Unlocking validated cells depending on the previous cell

Reveal questions.xls

Hello All,

This is my first post here so please be kind (I am no VBA expert that's for sure)

I have a simple protected spreadsheet that has a list of questions in rows grouped into 10 sets of 3-5 questions with yes no drop-down (validation) answers in column C.

SIMPLY (ha!), all I need to do for each question set is have the first "Yes/No" dropdown unlocked and the remaining questions in that set locked with a colour fill. With each "No" answer, the next cell in that column (row beneath) unlocks. The user answers "Yes" or "No" in question set 1 until they can go no further (i.e. they answer yes for a question) and thenmove onto question set 2, 3, 4 etc

e.g. C2 "Yes/No" is unlocked, C3:C5 are locked with pale yellow fill. IF C2 = "No", C3 "Yes/No" = unlocked and C4:C5 remain locked. IF C2 = "Yes", C3:C5 Remain locked and so on

This needs to be possible for all related question sets on the same sheet e.g C7 is unlocked and C8 is locked IF C7 = "No", C8 = unlocked. IF C8 = "Yes", C8 remains locked and so on.

Something like this but vastly expanded:

Private Sub Worksheet_Change(ByVal Target As Range)
If [C2] = "No" Then
ActiveSheet.Unprotect ("password")
[C3].Locked = True
[C3].Interior.ColorIndex = 0
ActiveSheet.Protect ("password")
Else
ActiveSheet.Unprotect ("password")
[C3:C5].Interior.ColorIndex = 36
[C3:C5].ClearContents
ActiveSheet.Protect ("password")
End If
End Sub

Please does anyone have a simple-to-understand answer. I have seen many similar queries, but none that fit.

Cheers