Conditionally lock a Cell

[Guest]

I have a holiday sheet where a number of the cells are locked but some can be
accessed to put in holiday requests.

The person inputs their holiday request in cell C15, the manager then
unlocks the worksheet and puts "Yes" into cell G15.

Is there anything I can do to make this "Yes" entry automatically tick the
lock cell option in C15 - so that the request cannot be retrospectively
changed once the approval has been made?

 

[Guest]

The only way I know of to do this is with a worksheet_change event.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count <> 1 Then Exit Sub
If Not Intersect(Target, Range("$G:$G")) Is Nothing Then
If LCase(Target.Value) = "yes" Then
With Target.Offset(0, -4)
.Locked = True
End With
End If
End Sub


You can read more about worksheet events here.

http://www.mvps.org/dmcritchie/excel/event.htm