Protect Individual Cell with Formula?

[golf4]

Hi, everyone -

I just had a quick query. Working on my income calculation tool, I ran into something I haven't tried before. First, my data entry sheet is fully protected, via password, except for cells I use to enter specific data for calculation.

What I'm wondering is what the best method is to accomplish the following (example): if cell A1 equals either 4 or 5, then how would I protect cell A2? I'm thinking it's got to be something like

=IF(OR(A1=4,A1=5),PROTECT(A2),"")

?

Or could the same thing be accomplished in Conditional Formatting? Any help would be great.

Thanks,

Golf

[Jacob Hilderbrand]

You can get tricky with validation, but the cell won't really be protected. What you need is VBA. Put this code in the sheet code section for the sheet you want it to work on. Change "Sheet1" to the actual sheet name, you can also use ActiveSheet if you prefer.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1").Value = 4 Or Range("A1").Value = 5 Then
        Sheets("Sheet1").Unprotect Password:="Password"
        Range("A2").Locked = True
        Sheets("Sheet1").Protect Password:="Password"
    Else
        Sheets("Sheet1").Unprotect Password:="Password"
        Range("A2").Locked = False
        Sheets("Sheet1").Protect Password:="Password"
    End If
End Sub

[golf4]

Hi, Jacob -
Thanks for the response. I really appreciate your suggestion.

Take care,
Golf

[XL-Dennis]

http://www.mrexcel.com/board2/viewto...=500582#500582

In general I would prefer that OP at least let the boards know that the same question has been asked on several boards so people who respond are aware of it.

Kind regards,
Dennis

[Jacob Hilderbrand]

Hi, Jacob -
Thanks for the response. I really appreciate your suggestion.

Take care,
Golf

You're Welcome

Take Care

[Zack Barresse]

Hi Golf,

Why not just skip the VBA and use Data Validation? You can use Custom, formula ...

=((A1<>4)+(A1<>5))<>1

[Jacob Hilderbrand]

That's what I meant by

You can get tricky with validation, but the cell won't really be protected

While the validation will stop you from typing into the cell, it unfortunately won't stop you from clearing the cell contents or from copy/paste.

[Zack Barresse]

Good point Jake. I was just thinking along the lines that simple may be better. If not, it's just another option for Frank.

[golf4]

Thanks, again, Jake and Zack -

KISS ( ya gotta love it!!!). I really appreciate all of the ideas. I had also posted my query on www.mrexcel.com (thread posted below) where I kind of expounded a little more of my actual situation. I figure I could always use my exposure to VBA, so I decided to go that way. Plus I figure I could always "cannibalize" the code I have now for other things. I've used Validation in so many other places in the tool as it is that Staff is getting real used to just "entering" through the warning message boxes without reading them.

http://www.mrexcel.com/board2/viewto...2946&highlight=

Dennis - Thanks for the suggestion.

Anyway, I really appreciate all the help from everyone.

Take care,

Frank