Tom Urtis, inside -->

Tom,
You helped me with a "Conditional Cell Locking" question I had. I was able to use your info to accomplish what I needed to do.
Thanks a lot!

Stan

Posted by Tom Urtis on January 20, 2002 at 21:59:09:

In Reply to: Re: COnditional Cell locking, Tom Urtis posted by Tom Urtis on January 20, 2002 at 09:59:44:

See if this is closer to what you want. Not sure where I'll be on Monday. Right click on sheet tab, left click on View Code, and paste this in. Note, it is for range
A2:D50, with the password as "PASSWORD", so modify as needed.

Private Sub Worksheet_Change(ByVal Target As Range)

'All conditions are for range A2:D50. Modify as needed

'Condition 1, if cell in column A is changed to anything except ""
If Target.Column = 1 And (Target.Row > 1 And Target.Row <= 50) Then
If Target.Value <> "" Then
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row
Range(Target, Target.Offset(, 3)).Locked = False
'Lock the 3 columns in that row not belonging to the target (B, C, D)
Range(Target.Offset(, 1), Target.Offset(, 3)).Locked = True
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
Else
'If the target cell was changed to a "" value
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row to allow any cell to be accessed.
Range(Target, Target.Offset(, 3)).Locked = False
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If

'Condition 2, if cell in column B is changed to anything except ""
ElseIf Target.Column = 2 And (Target.Row > 1 And Target.Row <= 50) Then
If Target.Value <> "" Then
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row
Range(Target.Offset(, -1), Target.Offset(, 2)).Locked = False
'Lock the 3 columns in that row not belonging to the target (A, C, D)
'First column A
Target.Offset(, -1).Locked = True
'Then columns C and D
Range(Target.Offset(, 1), Target.Offset(, 2)).Locked = True
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
Else
'If the target cell was changed to a "" value
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row to allow any cell to be accessed.
Range(Target.Offset(, -1), Target.Offset(, 2)).Locked = False
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If

'Condition 3, if cell in column C is changed to anything except ""
ElseIf Target.Column = 3 And (Target.Row > 1 And Target.Row <= 50) Then
If Target.Value <> "" Then
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row
Range(Target.Offset(, -2), Target.Offset(, 1)).Locked = False
'Lock the 3 columns in that row not belonging to the target (A, B, D)
'First columns A and B
Range(Target.Offset(, -2), Target.Offset(, -1)).Locked = True
'Then column D
Target.Offset(, 1).Locked = True
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
Else
'If the target cell was changed to a "" value
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row to allow any cell to be accessed.
Range(Target.Offset(, -2), Target.Offset(, 1)).Locked = False
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If

'Condition 4, if cell in column D is changed to anything except ""
ElseIf Target.Column = 4 And (Target.Row > 1 And Target.Row <= 50) Then
If Target.Value <> "" Then
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row
Range(Target, Target.Offset(, -3)).Locked = False
'Lock the 3 columns in that row not belonging to the target (A, B, C)
Range(Target.Offset(, -3), Target.Offset(, -1)).Locked = True
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
Else
'If the target cell was changed to a "" value
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row to allow any cell to be accessed.
Range(Target, Target.Offset(, -3)).Locked = False
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If

Else
Exit Sub

End If

End Sub

Any better?
Tom Urtis