Column B controls the "lockedness" of column A?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'one cell at a time
If Target.Cells.Count 1 Then Exit Sub

'only in column B
If Intersect(Target, Me.Range("B:B")) Is Nothing Then Exit Sub

On Error GoTo ErrHandler:

Me.Unprotect
Target.Offset(0, -1).Locked = CBool(Target.Value = 1)

ErrHandler:
Me.Protect
Application.EnableEvents = True
End Sub




mthatt wrote:

I need to lock cells in a column based on a value in another column.

So if value en column B is "1" then cell in same row in column A will be
locked else unlocked.

I have tried this setup:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Address = "$A$1" Then
ActiveSheet.Unprotect
ActiveSheet.Cells.Locked = False
If Target.Value = "1" Then
Range("B1:b10").Cells.Locked = True
End If
ActiveSheet.Protect
End If
enditall:
Application.EnableEvents = True
End Sub

But if only locks based on the value in af fixed celle A$1$, and i cant seem
to find have to adjust it, so condition in column A and locking af cell in
column B work for each row individual.

I've seen in other topics on this board, that data validation can be used -
ived tried it, and it works so that you cannot change data i the cell, BUT
you can delete!! - so the data validation is not an option for me in this
case.

Can anyone help me?

--

Dave Peterson