Solved: Lock cells based on cell value

[SDave]

Morning all,

Is it possible to lock a row based (to prevent further editing) on the value of a single cell?

I would like to lock columns O:AC if the value of column AB equals Leaver. Likewise I would like to lock columns O:W if the value of column AB equals Increase Post Jan.

I've tried using the following code (for part one of my question), however, instead of locking the cells mentioned it hides all formulas, locks the worksheet but still permits values to be entered in all cells?!

[VBA]
Private Sub Worksheet_Change(ByVal Target As Excel. Range)
Me. Unprotect
If UCase(Cells(Target.Row, 29)) = "Leaver" Then
Cells(Target.Row, 3).Resize(, 28).Locked = True
Else
Cells(Target.Row, 3).Resize(, 28).Locked = False
End If
Me.Protect
End Sub
[/VBA]

Any ideas? Any help would be much appreciated.

Thanks.

[joms]

Hi Sdave, in your statement you put ucase..
see:
If UCase(Cells(Target.Row, 29)) = "Leaver" Then

so that statement will execute the else statement..you compare "LEAVER" to "Leaver" how about changing the "Leaver" to "LEAVER" check what will happen.. so change your statement like this:

If Ucase(Cells(Target.Row, 29)) = "LEAVER" Then

try that and check the result Good luck!

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Me.Unprotect
Cells(Target.Row, 3).Resize(, 28).Locked = LCase(Cells(Target.Row, 29)) = "leaver"
Me.Protect
End Sub
[/vba]

[SDave]

Thanks joms, why I missed that I don't know?!

xld - much appreciated, as always. Your modified code works a treat. Is there anyway I could set the case to proper as opposed to either upper or lower, it's just for aesthetic purposes?!

I've tried a few different connotations trying to insert vbProperCase or Proper but I'm stumped, I keep receiving a compile error!!!

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Me.Unprotect
Cells(Target.Row, 3).Resize(, 28).Locked = Application.Proper(Cells(Target.Row, 29)) = "Leaver"
Me.Protect
End Sub
[/vba]

[SDave]

Thanks xld - much appreciated.