Protect or Unprotect Cell dependent on different cell
 

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

Protect or Unprotect Cell dependent on different cell
 

You could do this with a worksheet_change event to make it fully
automatic.Unlock ccell f1protect the sheetright click sheet tabview
codeinsert 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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David Bruce" <David wrote in message
...
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

Protect or Unprotect Cell dependent on different cell
 

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
--
HTH...

Jim Thomlinson


"David Bruce" wrote:

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