Hi,

I have some problem when using Worksheet_Calculate(), I want to write some
line to make cells change color when its value change after recalculate, I
use Worksheet_Calculate() but don't know how to locate the changed cells. For
Worksheet_Change(), we use Target.address to locate the changed cells
address, Do anyone know how to do the same thing with Worksheet_Calculate()?

Thanks alot.

Use static variables to keep track of the previous values of the cells you
are interested in.

Each time the Calculate Event occurs, the code would compare the values in
the static variables to the values in the cells.

This way the changed cell can be detected.
--
Gary''s Student - gsnu200831


"FourLegSpider" wrote:

Hi,

I have some problem when using Worksheet_Calculate(), I want to write some
line to make cells change color when its value change after recalculate, I
use Worksheet_Calculate() but don't know how to locate the changed cells. For
Worksheet_Change(), we use Target.address to locate the changed cells
address, Do anyone know how to do the same thing with Worksheet_Calculate()?

Thanks alot.

Thanks, I think it should work, actually I am using something like
yahoo_quote functions download stock quote from yahoo , 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)I don't know how many lines there will be.
2)if I change the price column to another column, I need to change the
code every time.



Gary''s Student wrote:
Use static variables to keep track of the previous values of the cells you
are interested in.

Each time the Calculate Event occurs, the code would compare the values in
the static variables to the values in the cells.

This way the changed cell can be detected.

For the second problem, consider assigning a Defined Name to the price
column. That way, if you add/remove columns, your code can track the price
column automatically.
--
Gary''s Student - gsnu200832


"SaltyBall" wrote:

Thanks, I think it should work, actually I am using something like
yahoo_quote functions download stock quote from yahoo , 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)I don't know how many lines there will be.
2)if I change the price column to another column, I need to change the
code every time.



Gary''s Student wrote:
Use static variables to keep track of the previous values of the cells you
are interested in.

Each time the Calculate Event occurs, the code would compare the values in
the static variables to the values in the cells.

This way the changed cell can be detected.

actually, I am new to excel vba, can you give some more hint about
"assigning a Defined Name to the price column"

do you mean use a variable as the name of the column variable? is it
possible in excel vba?

Thank you very much

Gary''s Student wrote:
For the second problem, consider assigning a Defined Name to the price
column. That way, if you add/remove columns, your code can track the price
column automatically.