color code cells based on changes to cell value

**wintermelon** · 02-17-2013, 09:07 AM

Hello,

I have a range of cells (e.g. A1:A10) with formulas whose values keep changing if the calculation mode is automatic, e.g. stock prices.
How do I write VBA code to color the cell green if the value goes up, red if the value goes down, and yellow if the value remains the same?

Thanks.

**Cheeky Charlie** · 02-17-2013, 11:14 AM

You'd need to store the previous value - the most logical place would be in an adjacent column. Then you wouldn't need code, just conditional formatting. Assuming you are running code to update the values, the same code could be used to copy the current values to the adjacent column before refreshing.

**wintermelon** · 02-17-2013, 11:02 PM

Hi CC

I don't have access to the code that updates the values. Cell A1 could be =STOCKPRICE("MSFT"), Cell A2 could be =STOCKPRICE("AAPL"), etc. The STOCKPRICE function updates the values in real-time and is provided by a third-party add-in, so I don't have control over the "refresh" in order to store and update the previous value before refreshing...

**Cheeky Charlie** · 02-18-2013, 07:23 AM

Regardless of your control over the process, you can't compare one value with another which doesn't exist (because it's been overwritten).

I think that means you would need two copies of each value so you could retain a current version which wouldn't get overwritten, and a previous version to do your actual comparison. The approach is - on sheet change, copy the new version to a 'current' copy after copying the 'current' to a 'previous' then conditional formatting would compare the live with the 'previous' - like so:

Start:
Cells: A1 | B1 | C1
=stockprice("MSFT") | <empty> | <empty>

code:
on worksheet change
disable events
copy B1 value to C1 then copy A1 value to B1
enable events

Refresh one:
=stockprice("MSFT") | <Value of A1 at time 1> | <empty>

Refresh 2
=stockprice("MSFT") | <Value of A1 at time 2> | <Value of A1 at time 1>

Apply conditional formats

So B1 would always = A1, except it doesn't get overwritten when A1 is updated

**wintermelon** · 02-22-2013, 03:29 AM

Hi CC,

Thanks for your help. I tried using the worksheet_change event but that doesn't trigger on update, I've put it under worksheet_calculate and that works.
I've also used a global array to store the prices and coloring the cells from VBA instead of putting in adjacent cells and using conditional formatting. I suspect that using a global array would be faster than cell access, but I've not verified that.

Thanks once again.