Excel cell reference to it´s previous value (conditional formatting)

Question

I have a good handle on Conditional Formatting in general. However, one use-case I have not been able to figure out yet, perhaps someone could give me a cue as to how to achieve it.

I am creating a MarketWatch listing of financial instruments on a sheet. Live data comes in and changes frequently.

My question - if the live price-feed for Stock ABC comes in as 5.00 USD, and then increases, say to 5.50 USD, I would like the cell to turn green. If the subsequent value now turns to 5.40 USD (which is less than the same cell´s "previous" value), I would like the cell to turn red. And so forth on a continuing basis.

The main point (which makes it tricky) is - the cell reference is dynamic - as it references to it´s own previous value), not a static "other cell".

What possibilities might exist to accomplish this?

Answer 1

You will need to do this manually, as it's not supported by the standard conditional formatting.

You could handle this event: Private Sub Worksheet_Change(ByVal Target As Range)

A way to do this is shown here: https://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change

You would need to compare Target with the current value of the cell. You could then set the colour of the cell using cell.Interior.ColorIndex = ...

--

Two things in response to the comment:

You are right that Change refers only to user-initiated changes. To capture recalculations too, you need to handle the event Calculate as well. Here's an example of that.

Private Sub Worksheet_Calculate()
  Call updateme
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  Call updateme
End Sub

These two functions call another subroutine updateme whenever either of those events occurs.

Within this function, you need to check if the value has changed, and then decide on the color change. This means keeping track of the previous value in a global variable. You could do this:

Private Sub updateme()
  Set cell = ActiveSheet.Range("A5")
  newval = cell.Value
  If newval > lastval Then
    cell.Interior.ColorIndex = 3
  End If
  If newval < lastval Then
    cell.Interior.ColorIndex = 4
  End If
  If newval = lastval Then
    cell.Interior.ColorIndex = 2
    End If
lastval = newval
End Sub

How do you keep track of that value, lastval, across function calls? You need to initialise the lastval variable as a global variable. To do that, you need to create a new "Module" in the VBA editor, and add this to the module:

Public lastval As Double