Locking Cells If Certain Values Is Selected

[jonny]

I wish to lock and color marked cells if selected "Not Must", otherwise to unlock and uncolor.
222.JPG
Will appreciate any help with the the best way to do that.

[jonny]

The solution!

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


    Dim ws As Worksheet
    Dim MustNot As String


    Set ws = ActiveSheet
    MustNot = ws.Range("B4").Value ' & PatientRow).Value


    ' unprotect the sheet so that we can modify locked settings
    ws.Unprotect
    ws.Range("C4:N4").Cells.Locked = False


    ' lock row
    Range("C4:N4").Cells.Locked = True
    Range("C4:N4").Interior.ColorIndex = 48




    ' unlock the row depending on answer
    If MustNot = "Must" Then
        Range("C4:N4").Cells.Locked = False
        Range("C4:N4").Interior.ColorIndex = 27
    End If


    ' protect the sheet again to activate the locked cells
    ws.Protect
End Sub