Flashing Cell for a number range

[tpeter]

I have a worksheet (sheet2, range a13:a100) that has a calculation for the
amount of flow. I want the cell to flash if the result is <200 or > 300, if
the cell is blank or not a number I don't want anything to happen. I have
found some similar postings but can't figure out what I am missing. Any help
would be greatly appreciated:


Public Sub Worksheet_Change(ByVal Target As Range)
Dim BlinkRange As Range

Set BlinkRange = Range("A1:A100")

If Not Intersect(Target, BlinkRange) Is Nothing Then
If Target.Value > 300 And Target.Value < 200 Then
StartBlink

If Target.Value = "" Then
StopBlink
End If
End If
End If
End Sub

Public Sub StartBlink()

Dim myBlinkRange As Range
Dim RunWhen As Long

Set BlinkRange = Range("a13:a100")

With myBlinkRange
If .Font.ColorIndex = 3 Then
.Font.ColorIndex = xlColorIndexAutomatic
Else
.Font.ColorIndex = 3
End If
End With

RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "StartBlink", , True

End Sub

 

[Bob Phillips]

Couple of things.

Did you store the StartBlink macro in a standard code module, and the
worksheet_change procedure in the worksheet code module?

This is an impossible condition

If Target.Value > 300 And Target.Value < 200 Then

It cannot possibly be > 300 and <200. You Want


If Target.Value > 300 Or Target.Value < 200 Then

 

[tpeter]

Bob,

Thank you for your response. The code is stored in the worksheet on Change
event, and you are correct it should have been Or not And. The code is still
not recognizing it, because no blinking happens once you add new data. Would
it matter that these cells are formula's? I checked it using isnumber and it
comes back true. Here is the formula if that helps =(D14-D13)/(24*(C14-C13))

 

[Bob Phillips]

Yes, that would make a difference.

Looking at your code, I foresee some problems. Do you want

 

[tpeter]

Bob,

What suggestions do you have, or is it more a pain than it is worth?

 

[Bob Phillips]

No, sorry I don't know what happened to that post.

What I was asking is

- do you want to have all cells in that range flashing if any value meets
the criteria, and all off if any are blank (problem is that both situations
may arise in a range)
- do you want each to flash separately (this will require some way to
control the separate timers)
- do you want just the last affected to change (this will be difficult as we
don't know which was changed when the calculate event kicks in).

 

[tpeter]

cell a13-a100 are the flow rates calculating the date check-previous date and
giving me calculation on how fast or slow they are running. I would like a
warning (flashing cell) when the calculation is not between 200 and 300. So
using this range there could possibly be 87 different dates. What I am trying
to accomplish is to have a cell flash when the formula comes up with
something outside of 200-300. This could be 1 cell or 3 cells, depending on
the date and if the problem was previously fixed.

We could make the whole range flash if one was out, but then it defeats the
purpose on later checks if the problem has been soved. Please let me know if
this information was helpful.

 

[Bob Phillips]

Yeah that was good and clear.

Flashing for a potential 87 cells is going to be difficult, would a coloured
background to the cell be sufficient instead?

 

[tpeter]

Bob,

Most of the time only 15-20 cells will be activated before the test is done.
Colored background works using standard conditional formatting. Was hoping to
get something that jumped out at the end user a little more.