Worksheet_Calculate() , How to find affected cells?

[SaltyBall]

Hi,

Worksheet_Calculate(), I want to make cells change color when its value
change after recalculate.

Worksheet_Change() can use "Target.address" to locate the changed cells

How to find affected cells for Worksheet_Calculate()?
Worksheet_Calculate() have no "Target.address"


Thank you.

 

[Bob Phillips]

I don't think that you can. The Calculation engine is all under the hood,
Excel uses a smart calculation model, that is it determines what cells need
recalculating but I don't believe that it exposes that data.

 

[SaltyBall]

Gary''s Student in microsoft.public.excel.worksheet.functions suggest me
to use static variables to keep track of the previous values of the
cells you are interested in, so that each time the Calculate Event
occurs, the code would compare the values in the static variables to the
values in the cells.

I think it should work but will cause some new problem, actually I am
using something like yahoo_quote functions download stock quote from
yahoo continuously, when the price go up, only the affected cell turn
green, when the price go down, only the affected turn red.

If use static variables, the main problem is
1)we don't know how many lines there will be and we need to code every cells
2)if we change the price column to another column, I need to change the
code every time.

any better suggestions?