Dave Bourgeois
New Member
- Joined
- Jan 24, 2005
- Messages
- 1
Hello. I'm a new member to the board with a specific flashing cell question. I know the topic has been covered before, and my code below was taken from various answers in this forum. But my question adds a formula into the target cell.
Basically, I need one cell (D9, for example) to flash when I've reached a certain value or above. Cell D9 contains a sum formula. The code copied below will not work unless I punch the number into D9 manually. How can I get this to work if the target cell is a formula?
Cell D9 has the formula: =SUM(D1:D8). When D9 is over 1.31, cell D9 should flash. But this works only if I type a number greater than 1.31 into cell D9.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 9 And Target.Column = 4 Then
If Target.Value > 1.31 Then
For n = 1 To 10
Target.Interior.Color = vbGreen
Delay (0.2)
Target.Interior.Color = RGB(57, 183, 1)
Delay (0.2)
Next n
End If
End If
End Sub
Sub Delay(rTime As Single)
'delay rTime seconds (min=.01, max=300)
Dim oldTime As Variant
'safety net
If rTime < 0.01 Or rTime > 300 Then rTime = 1
oldTime = Timer
Do
DoEvents
Loop Until Timer - oldTime > rTime
End Sub
How I could modify this so that the flashing cell D9 works for a formula?
Thanks for everyone's help in advance...
Dave.
Basically, I need one cell (D9, for example) to flash when I've reached a certain value or above. Cell D9 contains a sum formula. The code copied below will not work unless I punch the number into D9 manually. How can I get this to work if the target cell is a formula?
Cell D9 has the formula: =SUM(D1:D8). When D9 is over 1.31, cell D9 should flash. But this works only if I type a number greater than 1.31 into cell D9.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 9 And Target.Column = 4 Then
If Target.Value > 1.31 Then
For n = 1 To 10
Target.Interior.Color = vbGreen
Delay (0.2)
Target.Interior.Color = RGB(57, 183, 1)
Delay (0.2)
Next n
End If
End If
End Sub
Sub Delay(rTime As Single)
'delay rTime seconds (min=.01, max=300)
Dim oldTime As Variant
'safety net
If rTime < 0.01 Or rTime > 300 Then rTime = 1
oldTime = Timer
Do
DoEvents
Loop Until Timer - oldTime > rTime
End Sub
How I could modify this so that the flashing cell D9 works for a formula?
Thanks for everyone's help in advance...
Dave.