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

P

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?
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

if cell colour is then change cell colour in range 2
Cell Shading Colour Property 8
UDF Frustration - Won't Update 3
Interior Cell Colour - Conditional Formatting 3
Microsoft Excel VBA: Concatenate without formatting 1
custom function - finding cell address 4
Excel Conditional Formatting Removal 7
Cell colour format according to date 1

Top