Using protected cells

maddyc 

In the first place, ActiveSheet.Range("C2") will never be Nothing - it is an object with lots of properties. So the code below won't be executed.

In the second place, locking cells only works if the sheet as a whole is protected. The code will have to unprotect the sheet, lock or unlock some cells, then protect the sheet again. You can use

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C2"), Target) Is Nothing Then
        Me.Unprotect
        Range("B43:J49").Locked = (UCase(Range("C2").Value) = "X")
        Me.Protect
    End If
End Sub

See the small sample workbook attached to this reply (you'll have to allow macros).