Protect Individual Cells by Formula?

You don't protect cells based on formulas. Cells that contain formulas can be protected, tho, and will still calculate the formulas, but they're saved from being overwritten.

If that doesn't help, why dontcha tell us exactly what you're trying to do so we can help better?

See if this helps. Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Modify for password.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Or Target.Cells.Count > 1 Then Exit Sub
If Target.Value = 4 Or Target.Value = 5 Then
ActiveSheet.Unprotect ("YourPassword")
Range("A2").Locked = True
ActiveSheet.Protect ("YourPassword")
Else
ActiveSheet.Unprotect ("YourPassword")
Range("A2").Locked = False
ActiveSheet.Protect ("YourPassword")
End If
End Sub

Hi, Anne and Tom -

Thanks for the responses. I really appreciate it. Just a little more clarification on my question. I've included the actual cell references:

1. My Data Entry Sheet is password-protected except for a select number of data entry cells like H36;

2. Basically, what I want to happen is first to look at the MIN of cells E40 (bedroom size) and H42 (Voucher size);

3. If the MIN of E40 and H42 is either 4 or 5, then I was looking to have cell H36 locked off - otherwise leave it untouched;

Anne - I was originally looking to insert the "locking" formula in an adjacent cell to H36 (I36) and then hide and protect I36 so it can't be overwritten.

Tom - Is there a way to modify your VB code to accomplish this? Would I still have to unprotect the whole sheet if H36 is already unprotected?


Thanks to both of you for your help,

Golf

Better stick with Tom, Golf. A wee bit over my head!

Replace the first procedure I posted with this one in the sheet module, and see if it gets you closer to what you really need.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E40,H42")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If Application.Min(Range("E40,H42")) = 4 Or Application.Min(Range("E40,H42")) = 5 Then
ActiveSheet.Unprotect ("YourPassword")
Range("H36").Locked = True
ActiveSheet.Protect ("YourPassword")
Else
ActiveSheet.Unprotect ("YourPassword")
Range("H36").Locked = False
ActiveSheet.Protect ("YourPassword")
End If
End Sub

Thanks for the help, Tom -
Your suggested code works perfectly!!!!

Thanks again and take care,

Golf

Hi, Tom -
One additional question: I attempted to modify your code to lock off one additional cell (cel H37 as bolded below), but the code seems to be locking only cell H36. Am I doing something wrong in modifying it in this manner?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E40,H42")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If Application.Min(Range("E40,H42")) = 4 Or Application.Min(Range("E40,H42")) = 5 Then
ActiveSheet.Unprotect ("led52not")
Range("H36").Locked = True
Range("H37").Locked = True
ActiveSheet.Protect ("led52not")
Else
ActiveSheet.Unprotect ("led52not")
Range("H36").Locked = False
Range("H37").Locked = False
ActiveSheet.Protect ("led52not")
End If
End Sub

Thanks for the help,
Golf

Hey Tom -

OK, IT'S LATE!!!!!

I went ahead and saved the tool and now all of the cells I need locked off works perfectly. Again, thanks for the help......

Golf

Not a big deal, but FYI you can combine those two code lines of yours into one line, two different ways in your case, for more efficient code:

You have:
Range("H36").Locked = True
Range("H37").Locked = True


Can be either
Range("H36:H37").Locked = True 'or False
because they are contiguous cells

or

Range("H36,H37").Locked = True 'or False
because they are separate cells.

In any case it looks like you have what you need.