Lock a range of cells based off a value of another cell and duplicate for multiple rows

How do cells in column K change. Are they manually changed or do they contain formulae that change as a result of some precedent cells elsewhere?

If they're manually changed then

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("K:K")) Is Nothing Then

        If Target = "Yes" Then
            Range("L" & Target.Row & ":T" & Target.Row).Locked = False
            Range("X" & Target.Row & ":Y" & Target.Row).Locked = False
        Else
            Range("L" & Target.Row & ":T" & Target.Row).Locked = True
            Range("X" & Target.Row & ":Y" & Target.Row).Locked = True

        End If
    End If

End Sub

Hi,

If a sheet is unprotected then whether a cell is locked or not is irrelevant. A locked cell is only prevented from being changed when sheet protection is in place.

Please upload the workbook so that we can establish the problem.

Incidentally, and for reference. In VBA rather than use the sheet tab name - which is too easily changed by a user and would cause a macro to fail, use the VBA Sheet code name. So instead of

Worksheets("04_Control Assessment")
use
Sheet4.Range("blah blah ' assuming 'Sheet4' is indeed the VBA code name.

Hi,

In the 04 Assessment sheet you are testing for the Target being "Yes".
In a sheet change event the target is the cell that's being changed.

When you click the button on the Information sheet, that clears the contents in K3:T480 on the 04 sheet but at that time the target is the whole K:T range and hence when the sheet change event comes to the check for the target = "Yes" it doesn't understand what K:T = "Yes" means

i.e. there are two events that change the sheet and hence the sheet change macro. One where you manually change a cell in column K, and the other when another macro clears the contents of a range.

One way round this would be to trap the error and ignore it. So use

 On Error Resume Next
        If Target = "Yes" Then
        On Error GoTo 0

The On Error Goto 0 cancels the On error Resume trap since it's not usually wise to let code bypass errors without undertsanding why an error might have occurred.