Protect Individual Cells by Formula?

Thanks, Tom & Dennis -

Tom - your suggestion really gives me a better insight into VB.

Thanks again,

Golf

Thanks for the compliment ! A lot of people on this and other boards have their own style, so it's always a good idea to see how different people attack the same problem, and then test for yourself their approaches to see what works best for you.

Hi, Tom -

One more follow-up if it's ok:

The code works great to lock off the two cells using the MIN criteria. As well, I've kind of gotten "fancy" by adding a message/warning box (within VB) that refers to the locked "fields" (cells).

What I'm wondering is whether there is a way to modify my (did you pick up on the word "my" :wink: - not a proud man here) existing code to include coloring and flashing (for say 10 seconds) cells H36 & H37 assuming they meet the criteria in the code? Right now, I've included some Conditional Formatting that colors the cells, but I thought since I have the code to lock them off based on the same criteria, why not use it to the fullest.

Thanks again for the help,

Golf

Gee you don't ask for much on your posts do you?

First, what the heck is that animal swinging a golf club below your picture...is that a gopher, or some oversized chipmunk on steroids, or what?

Second, eliminate the Conditional Formatting for H36:H37 you spoke about.

Third, replace the code I gave you with this, and see if it gets you closer to what you want regarding the blinking effect.


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")
Dim BlinkRange As Range, i As Integer
Dim BlinkFactor As Double, BlinkBegin As Double, BlinkWait As Double
Set BlinkRange = Range("H36:H37")
BlinkFactor = 0.08
Do
DoEvents
BlinkBegin = Timer
BlinkWait = BlinkBegin + BlinkFactor
Do
DoEvents
BlinkRange.Interior.ColorIndex = 3
Loop Until Timer > BlinkWait
BlinkBegin = Timer
BlinkWait = BlinkBegin + BlinkFactor
Do
DoEvents
BlinkRange.Interior.ColorIndex = 4
Loop Until Timer > BlinkWait
i = i + 1
Loop Until i = 25
BlinkRange.Interior.ColorIndex = 3
Set BlinkRange = Nothing
Range("H36:H37").Locked = True
ActiveSheet.Protect ("led52not")
Else
ActiveSheet.Unprotect ("led52not")
With Range("H36:H37")
.Locked = False
.Interior.ColorIndex = 0
End With
ActiveSheet.Protect ("led52not")
End If
End Sub

Hey Tom -

Well, I told you I wasn't proud!!!

As far as my avatar, I tried to find a picture that, somehow, matched my golf game..... it's kind of how I feel on the 1st tee early in the morning after a wild night of Excel. Yes, my social life even depresses me!!!

Thanks so much for the revised code. After getting rid of the Conditional Format stuff, and pasting your code, it works beautifully.

I'll leave you alone......... ah...... for a little while anyway.

Take care,

Golf