Case Satement

[deelee]

I've been trying, unsuccessfully, to modified a case statemen
that I was kindly helped with some weeks ago. My cry for help at tha
time was a resounding success and I am hoping for a similar resul
today!

One of my statements is:

Case "$C$3"
If Target.Value = "No" Then
ActiveSheet.Unprotect ("mypassword")
Range("C:C").Locked = True
Range("$C$3").Locked = False
ActiveSheet.Protect ("mypassword")
ElseIf Target.Value = "Yes" Then
ActiveSheet.Unprotect ("mypassword")
Range("$C$6:$C$10,$C$16:$C$20,$C$26:$C$30,$C$36:$C$40,$C$46:$C$50").Locke
= False
ActiveSheet.Protect ("mypassword")
End If

Which works just as it should, even after me playing with it! however
I now find that not all of the ranges in line 8 need to remain locke
but the pattern is random across twelve worksheets!

I have a cell which looksup if a range needs to be locked and in th
case of the first range ($C$6:$C$10) it is cell $C$4$ - this cell i
relative in each other case and if the range needs to be locked th
cell will hold "PH". Using this, Conditional Formatting changes th
appearance of the ranges. (Why couldn't cell locking be a feature o
Conditional Formatting? - no doubt someone will be able to enlighten m
).

I have tried using Offset() and Resize() from the active cell withou
success and was hoping one or two of you kind people would help m
out!

Thanks in advance,

Dav

 

[deelee]

It's me again!

I've played around with the statement but still can't get it to work!

I thought it might help if I post my latest attempt so that you can see
where I'm going wrong!

Case "$C$3" 'Monday

'Non-working day
If Target.Value = "No" Then
ActiveSheet.Unprotect ("MyPassword")
Range("C:C").Locked = True
Range("$C$3").Locked = False
ActiveSheet.Protect ("MyPassword")

'Working Day
ElseIf Target.Value = "Yes" Then
ActiveSheet.Unprotect ("MyPassword")

Range("$C$6:$C$10,$C$16:$C$20,$C$26:$C$30,$C$36:$C$40,$C$46:$C$50").Locked
= False
ActiveSheet.Protect ("MyPassword")

'Public Holiday
ElseIf Target.Offset(1, 0) = "PH" Then
ActiveSheet.Unprotect ("MyPassword")
Range("$C$6:$C$10").Locked = True
ActiveSheet.Protect ("MyPassword")

EndIf

Thanks again

Dave