Protect or Unprotect Cell dependent on different cell

[David Bruce]

I am trying to find a way of automatically protecting a cell if an adjacent
cell has a specific value whilst if that cell has a different value the cell
would be unprotected.

For example if I am aiming to have a column with values in that will be
entered but if a field next to it has a 1 in the value field will then be
locked. But if the 1 is changed to a 0 the value field could then be edited.

Many Thanks for any help.

David

 

[Don Guillett]

You could do this with a worksheet_change event to make it fully
automatic.Unlock ccell f1>protect the sheet>right click sheet tab>view
code>insert this. Now when you change f1 it will lock/unlock g1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("f1").Address Then Exit Sub
With ActiveSheet
..Unprotect
If Target = 1 Then
.Range("g1").Locked = True
Else
.Range("g1").Locked = False
End If
..Protect
End With
End Sub

 

[Jim Thomlinson]

My preference for that is to use data validation with a custom validation.
Then you can use a formula to check the values of other cells to either allow
or disallow entry. Fot ease of use to your end users you may want to add a
conditional format to indicate when the cell is available to be edited...

Data | Validation | Custom | Add a formula and uncheck Ignore Blanks