Flashing green when cell value increases, red when decreases

Question

I have a feed in my excel sheet and want the cell to flash green / red if the value changes up or down respectivey. Can someone help on this? Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KeyCells As Range

   Set KeyCells = Range("B1:B27")

   If Not Application.Intersect(KeyCells, Range(Target.Address)) _
          Is Nothing Then

       Target.Interior.ColorIndex = 36

   End If
End Sub

Thanks for the help

Something not mentioned below, if you don't want to have secondary columns for previous values, you can use `Application.Undo` in a `Worksheet_Change` event to get the previous value of a cell, and then `.Redo`, and compare the two. Be careful if the `Worksheet_Change` range is larger than one cell (i.e. paste actions) or caused my a macro changing the cell, in which case undo history is often disabled. — Alain, Mar 15 '13 at 19:40

score 0 · Answer 1 · answered Mar 15 '13 at 13:31

... You can't really make it flash, but you can change the shading...

The way I would do it is, somewhere not being used in the workbook, create a set of cells that will store the cells old value (you can set these cells in the Worksheet_Change event), then it's just a matter of creating a conditional format rule for your cells to compare your cell's value against the helper cell's value - If larger, shading = green, if smaller, sahding = red.

Hope this makes sense.

score 0 · Answer 2 · edited May 23 '17 at 12:31

As John Bustos said, you need another column that houses the previous value of the cell. This value is necessary in order to do comparison to discern if the target is now higher or lower.

In order to achieve a flash, you need to set a pause in the code without freezing up the main UI. This code isn't tested or guaranteed to work as you want, but it should serve as a very good starting point.

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KeyCells As Range
   Set KeyCells = Range("B1:B27")

   If Not Application.Intersect(KeyCells, Range(Target.Address)) _
          Is Nothing Then
        If Target.Value > Cells(Target.Row, 5).Value Then
            'flash green
            Target.Interior.ColorIndex = 10
            Pause 0.5
            Target.Interior.ColorIndex = 2
            Pause 0.5
            Target.Interior.ColorIndex = 10
        ElseIf Target.Value < Cells(Target.Row, 5).Value Then
            'flash red
            Target.Interior.ColorIndex = 3
            Pause 0.5
            Target.Interior.ColorIndex = 2
            Pause 0.5
            Target.Interior.ColorIndex = 3
        End If
        Cells(Target.Row, 5).Value = Target.Value
   End If
End Sub

'Pauses execution without holding up main UI thread
Public Function Pause(NumberOfSeconds As Variant)
    On Error GoTo Error_GoTo
    Dim PauseTime As Variant
    Dim Start As Variant

        PauseTime = NumberOfSeconds
        Start = Timer
        Do While Timer < Start + PauseTime
            DoEvents
        Loop

Exit_GoTo:
       On Error GoTo 0
       Exit Function
Error_GoTo:
       Debug.Print Err.Number, Err.Description, Erl
      GoTo Exit_GoTo
End Function

Open the Visual Basic Editor (VBE) and double click Sheet 1, then paste the above code. Now you'll notice as you change the values within the targeted range B1:B27 a value with be placed in column E. You can change this in the code above by replacing 5 in Cells(Target.Row, 5).Value with whatever column you'd like.

Now as the values increase or decrease, the cell will flash to reflect the change.

The Pause function was found on this SO Post.

Instead of `Pause`, can you use `Application.OnTime` to register an alarm to revert to the original cell background color? — kevinarpe, May 09 '16 at 05:21

Flashing green when cell value increases, red when decreases

2 Answers2