Blinking Cell

[data808]

Is it possible to have a cell blink when numerical value changes? I would want either the cell or font to blink red whenever the value changes until it reaches 150 or more. Once the value becomes 150 or more, I would like it to flash green. There will be a decimal in there too sometimes, for example, 71.59 or 122.19. So anything 149.99 and under blink red and anything above that blink green. Is this possible? Thank you.

 

[Jaafar Tribak]

Is the value in the cell the result of a formula or of user input ?

 

[data808]

Is the value in the cell the result of a formula or of user input ?

The cell is locked from user input and is formulated with "=CONCATENATE(K99)*1" in it. Not sure why I used concatenate as there is only numbers in it but the cell it was pulling from had text and numbers in it. So I think I just didn't update it.

 

[Jaafar Tribak]

Cool. I will be logging off shortly but I will post something later. I am sure if you look around you will find plenty as this question has been asked hundreds of times... I am sure I have done something similar before. I just need to find the code.

 

[data808]

Cool. I will be logging off shortly but I will post something later. I am sure if you look around you will find plenty as this question has been asked hundreds of times... I am sure I have done something similar before. I just need to find the code.

Ok thanks. I been trying to look for something but haven't found anything yet. Will keep looking. Thanks again.

 

[bebo021999]

Is value of K99 from formula or user input?
What formula?
We need to find the source to see if we can use worksheet_change event, to trigger its change, to drive color from red to green or vice versa

 

[data808]

Is value of K99 from formula or user input?
What formula?
We need to find the source to see if we can use worksheet_change event, to trigger its change, to drive color from red to green or vice versa

Ok so K99's formula is this "=SUM(K94:K98)" so just your basic adding of a few cells. Then I had Q89 with this "=CONCATENATE("Work Units: ",K99)" and then R6 and R7 merged has this "=CONCATENATE(K99)*1". Oh I just remembered, the *1 was for a conditional formatting that I did so it could count it when the value got past 149.99 the font in the cell would turn green from red. So that was one indication for the user to know they made their quota for the day. Now I just want to add a blinking color to that to make it pop more.

So R6 and R7 merged is the cell that I want to blink a color to draw attention for the user to see it. Is it possible to make it only blink for a few seconds and then stop even if the user continues to go over the 150 value? In other words, I only want it to blink one cycle the first time they go over 149.99 to 150 or more. Once they are at 150 or more and continue to increase this number, no blinking required.

 

[Jaafar Tribak]

Here is an easy do-loop code for flashing cell A1 in Sheet1 when the formula in A1 is re-calculated.

Code goes in The ThisWokbook Module.

Option Explicit

Private Const FLASHING_CELL = "Sheet2!A1" '<<= change cell addrss to suit.
Private Const PEAK_VALUE = 150            '<<= change value to suit.

Private dblPreVal As Double
Private bFlashing As Boolean


Private Sub Workbook_Activate()
 bFlashing = False
    dblPreVal = Range(FLASHING_CELL)
End Sub


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If Sh Is Range(FLASHING_CELL).Parent Then
        With Range(FLASHING_CELL)
            If VarType(.Value) = vbDouble Then
                Select Case True
                    Case (.Value >= PEAK_VALUE) And (dblPreVal < PEAK_VALUE) And bFlashing = False
                        Call FlashCell(Range(FLASHING_CELL), vbGreen)
                    Case (.Value < PEAK_VALUE) And (dblPreVal >= PEAK_VALUE) And bFlashing = False
                        Call FlashCell(Range(FLASHING_CELL), vbRed)
                End Select
                dblPreVal = .Value
            End If
        End With
    End If
End Sub

Private Sub FlashCell(ByVal Cell As Range, ByVal lColor As Long)
    Dim t As Single, lInitColor As Long
    
    On Error GoTo Xit
    Application.EnableCancelKey = xlErrorHandler
    Beep
    t = Timer
    With Cell
        lInitColor = .Interior.ColorIndex
        Do While Timer - t <= 4
        bFlashing = True
        DoEvents
            If Int(Timer) Mod 2 Then
                .Interior.Color = lColor
            Else
                .Interior.ColorIndex = lInitColor
            End If
        Loop
Xit:
        .Interior.ColorIndex = lInitColor
        bFlashing = False
    End With
End Sub

 

[Jaafar Tribak]

Change Private Const FLASHING_CELL = "Sheet2!A1" to:
Private Const FLASHING_CELL = "Sheet1!A1"

 

[data808]

Change Private Const FLASHING_CELL = "Sheet2!A1" to:
Private Const FLASHING_CELL = "Sheet1!A1"

I just tested it in a blank workbook and it's working. Now I'm going to try and apply this to the real file. Will let you know if it works.