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.
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.
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