How do I lock a cell based on content of another cell?

[Alan T]

I'm probably over-thinking this one... but how do I have one cell that is locked for access when the value on another cell is greater than 1?

Example below explains my requirements better.

Column
A B C D
Row 1Level Cost Time Resource
2 1 £5 1:00 0.5
3 1.1
4 1 £7.50 0:30 1.0

I need to lock the cells in Columns B C & D for any sub-level (i.e B3, C3, and D3) in order that the user cannot input any value.

Anyone able to offer me any guidance?

Thanks in advance.

[Carim]

Hi,


It is feasible with an event macro ... but which cells are the triggering ones ?

Carim

[Alan T]

The triggering entries will be the contents of the cells in Column A.

[Carim]

Hi,

Provided you are working with all of cells in column A formatted as unprotected ... following macro will do the job ...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Me.Range("A:A")) Is Nothing Then Exit Sub
If .Value = 1 Then
.Offset(0, 1).Locked = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Else
.Offset(0, 1).Locked = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
End If
End With
Application.EnableEvents = True
End Sub

HTH
Cheers
Carim