Automatic evaluation of custom worksheet function

[Guest]

Hi,

I've created a short custom function that returns TRUE or FALSE if one or
more cells within the specified range is colored red. The function works
correctly, but if the interior color of one of the specified cells ever
changes, the function doesn't dynamically re-evaluate itself... I have to
manually force the formula cell to evaluate the function by double clicking
and hitting enter.

Any ideas?

Thanks.

 

[Biff]

That's why you shouldn't base calculations on formats! Changing a format
doesn't trigger a calculation. I'm not real knowledgable about VBA but you
can insert a line of code at the beginning of your UDF that makes it
volatile:

Application.Volatile

This STILL will not cause it to recalculate when the format is changed but
it will recalculate ANY time a sheet calculation takes place.

I don't know why the experts don't discourage calculations based on
color/format.

Biff