Conditional locking/deleting?

[aitzko]

Hi,

My question is: I'd like to lock and delete the contents of several cells depending on the content of a different cell. More specifically,

If F4 has any value at all (the options are "Individual", "Individual + Spouse", "Individual + Child" and "Family" -- my spreadsheet is about health care coverage!), I want to lock cells F5 and F6 and delete the contents of A5:AJ5 and A6:AJ6. (Not delete the cell, just the contents)

Is this even possible? Thanks very much in advance!

Amanda

 

[ChrisM]

You can do this with VBA. Have you used it before, do you want to?

 

[lenze]

Hi and welcome to the Board!!!
Use a WorkSheet_Change Event

If Target.Address <> "$F$4" Then Exit Sub
ActiveSheet. UnProtect "password"
Range("$F$5:$F$6").Locked = True
Range("$A$5:$AJ$6").ClearContents
ActiveSheet.Protect "password"
End Sub

Goes in the WorkSheet module. RightClick the sheet tab and choose "View Code"
lenze

 

[aitzko]

Thank you both for the very quick response!

lenze, thank you! I'm afraid I demonstrate my ignorance, however, when I say that after I put that code in, what do I do? Haha, I have never used the Visual Basic code stuff before, this is entirely new to me. Do I need to click anything to activate that?

Thanks again, and sorry for my woeful ignorance!

 

[lenze]

Sorry, the 1st line of code got omitted!!!!

Private Sub Worksheet_change(Byval Target as Range)
If Target.Address <> "$F$4" Then Exit Sub
ActiveSheet. UnProtect "password"
If Target <> "" Then
    Range("$F$5:$F$6").Locked = True
    Range("$A$5:$AJ$6").ClearContents
    ActiveSheet.Protect "password"
End If
End Sub

Place this in the WorkSheet module. Rightclick the sheet tab and choose "View Code" This macro will fire whenever $F$4 is changed.
lenze