Conditional Cell Locking/Unlocking
DeeLee, Ken's solution should work for you if < "Yes". To answer your 2nd
question about unlocking If ="Yes", I have added that code for Case $C$1,
you can fill in the rest.
Case "$C$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$C$4:$C$7,$C$12:$C$15,$C$20:$C$23,$C$28:$C$ 31,$C$36:$C$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
ElseIf Target.Value = "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$C$4:$C$7,$C$12:$C$15,$C$20:$C$23,$C$28:$C$ 31,$C$36:$C$39"). _
Locked = False
ActiveSheet.Protect ("MyPassword")
End If
Mike F
"Ken Johnson" wrote in message
oups.com...
Hi Deelee,
does this do what you want?...
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$C$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$C$4:$C$7,$C$12:$C$15,$C$20:$C$23,$C$28:$C$ 31,$C$36:$C$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case "$D$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$D$4:$D$7,$D$12:$D$15,$D$20:$D$23,$D$28:$D$ 31,$D$36:$D$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case "$E$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$E$4:$E$7,$E$12:$E$15,$E20:$E$23,$E$28:$E$3 1,$E$36:$E$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case "$F$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$F$4:$F$7,$F$12:$F$15,$F$20:$F$23,$F$28:$F$ 31,$F$36:$F$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case "$G$1"
If Target.Value < "Yes" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$G$4:$G$7,$G$12:$G$15,$G$20:$G$23,$G$28:$G$ 31,$G$36:$G$39"). _
Locked = True
ActiveSheet.Protect ("MyPassword")
End If
Case Else
Exit Sub
End Select
End Sub
There were a few typos in the Range addresses too eg "." instead of ","
and ":," instead of ",".
Ken Johnson
|