Option Explicit
Private Sub Worksheet_Calculate()

Dim myRngToCheck As Range
Dim myCell As Range

Set myRngToCheck = Nothing
On Error Resume Next
Set myRngToCheck = Intersect(Me.Range("b:b"), Me.UsedRange)
On Error GoTo 0

If myRngToCheck Is Nothing Then
Exit Sub
End If

Me.Unprotect
On Error GoTo ErrHandler:
For Each myCell In myRngToCheck.Cells
myCell.Offset(0, -1).Locked = CBool(myCell.Value = 1)
Next myCell

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



mthatt wrote:

Dear Dave

Again thak you for your input.

It works just fine when the locking i controlled by an inputed i column B,
but what if I want it to be controled by the result of an formula i column B
instead? (column B will be lock at all times)

Hope you can help again.

"Dave Peterson" skrev:

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


--

Dave Peterson