Web Update Causes Change in Cell Value

[Shatin]

I have a worksheet which retrieves data from the web. The data in the sheet
is updated regularly every few minutes. Sometimes I'd also update the data
manually. What I want to do is to highlight changes in the value of a cell,
say A1. For example, the value of A1 before update is 3%, after update 5%.
The change is 2%. I want to highlight A1, say by changing the font to green,
to signify a change of 2% or more. I know I need to put some code in the
worksheet_change module but I don't how to write the code to capture the
change in the same cell.

A related question. After an update, the color of the font in A1 has been
changed to green. Now there's another update. This time the change in A1 is
less than 2%. I need to change the font back to black (or automatic rather),
because if A1 stays green, there might be misunderstanding that A1 has
undergone another change which is greater than 2%. How can I do it?

 

[Nigel]

You are going to need to keep a record of the value of any cell(s) that are
likely to change so that when changes do occur you can compare the
difference. The worksheet_change event will be too late as the change will
have over written the previous value(s)!

I would suggest you keep two workbooks, the second being a copy of the
first. The first worksheet receives the changed data, whilst the second
will always be the prior version. You can then report the difference and
use the conditonal formatting options to change colors, fonts etc., based on
the conditions you are monitoring.

The challenge is controlling when to copy the first sheet onto the second.
This will be required to occur before an update occurs to the first sheet
not after. Therefore your code that causes the update to occur must be
changed to trigger a copy to take place before update. ( there is not before
change event at worksheet level). Post your code and someone will advise on
how to modify it.

An alternative approach would be to record the the monitored values into
another worksheet column(s) row by row with a date-time stamp this way you
would build up a history of the changes, but this would require more
extensive code.

Cheers
Nigel