While waiting for clarification on what's wanted could I point out that GTO's code can be shortened somewhat. GTO did set the UserInterfaceOnly option to True when protecting the sheet, and so repeated protecting/unprotecting the sheet to lock/unlock cells etc. is not needed. This works:[vba]Option Explicit
Const PWORD As String = "MyPassword" '<--- Change to password you have the sheet protected with.
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Protect Password:=PWORD, UserInterfaceOnly:=True 'this only needs to be done once, perhaps elsewhere eg. on saving the workbook, or activating this sheet.
If Not Application.Intersect(Target, Range("G2:G20")) Is Nothing And Target.Count = 1 Then
Select Case Target.Value
Case "Blocked", "Not Entered"
With Target.Offset(, 2)
.Locked = True
.Interior.ColorIndex = 48
.Interior.Pattern = xlSolid
End With
Case "Entered"
With Target.Offset(, 2)
.Locked = False
.Interior.ColorIndex = xlNone
End With
End Select
End If
End Sub[/vba](I've used Select Case only 'cos I find ElseIfs difficult..)
ps. The bit [vba] m = (Target.Column + 1) Mod 26
ColName = Chr(65 + m) + ColName
[/vba]seems to give the column letter header of the cell 2 cells to the right. It breaks down once you get beyond Z. You shouldn't need to do this in the code anyway, but a more reliable way is:[vba]ColName = Split(Target.Offset(, 2).Address, "$")(1)[/vba]