SDave
12-21-2009, 04:31 AM
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?!
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
Any ideas? Any help would be much appreciated.
Thanks.
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?!
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
Any ideas? Any help would be much appreciated.
Thanks.