function to re-calculate when cell format (colour) changes

[PBcorn]

I have a UDF which looks up all cells of a particualr colour in a range and
sums the cell values. However, the function does not re-calculate when the
colour of the cells in the range changes. How can I get this to work?

 

[Charles Williams]

You will need to make your UDF volatile:
- add somewhere near the start
Application.Volatile

This will make the function recalculate each time Excel recalculates, but
just changing the colour and nothing else does not trigger the need for
Excel to calculate.

So if the only change you make is the colour of a cell you need to press F9
to make Excel calculate.

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com