Locking selected cells in row when A column of the row is Yes for all rows

Question

I have a spreadsheet where column A asks a Yes/No question, I need the spreadsheet to lock cell columns C, D, E and J for that row. I need this action to be performed on every row.

So every row starts with the same Yes/No question then some cells are locked or left unlocked depending on the answer to Yes/No.

I have been working with some sample code I found online however it only works for a specific row and I am unsure how to make if apply to all rows. The sample code I found was:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A*") = "Yes" Then
        Range("B1:B4").Locked = False
    ElseIf Range("A1") = "Refusing" Then
        Range("B1:B4").Locked = True
    End If
End Sub

What I would like to know if how to create a wildcard to apply this code to a range, and also how to have a range such as cells C*:E*, J* to be locked, I am not sure how to have both a range and another cell that is not in the same direct range included.

Answer 1

Try the following code (untested). As @Comintern mentioned, Target is the range that has been changed. Note, a range can be a single cell. Using the .Row property returns the row of the changed cell.

Cells allows you to define the row and column as two separate values. And Range can be used in conjunction with Cells to get a range of cells.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo esub   'If an error occurs reprotect the sheet
Me.Unprotect   'Remove sheet protection to avoid runtime error

If Target.Column=1 and Target.Value="Yes" Then
    Range(Cells(Target.Row,"C"),Cells(Target.Row,"E")).Locked=False
    Cells(Target.Row,"J").Locked=False

ElseIf Target.Column=1 and Target.Value="Refusing" Then
    Range(Cells(Target.Row,"C"),Cells(Target.Row,"E")).Locked=True
    Cells(Target.Row,"J").Locked=True
End If

esub:
Me.Protect    'Reprotect sheet
End Sub