AutoRunning User Defined Formula

[Parlablane]

Greetings All from a Newbie to the boards.

I have an Excel file (zip attached) that shows the utilisation of various network switches we have on site.

I've managed to get it count how many ports are in use (count cells in yellow) and how many ports are available in a room (count cells using a certain font and subtract those which are already in use (coloured yellow).

But (and this is a big but) I cannot make the formula auto run when I change the colour of a cell. If I change the value of the cell the formula will update, but I don't do that. I just change the colour of the cell.

At the moment, I have to double click and hit enter for each cell that I want to update.

My question is - "Can I set Excel to automatically upate each cell when anything is changed?" or "Is it possible to add a button the will run all formula on a page?"

Any help or suggestions would be very welcome.

Thank you all.

[Bob Phillips]

But (and this is a big but) I cannot make the formula auto run when I change the colour of a cell. If I change the value of the cell the formula will update, but I don't do that. I just change the colour of the cell.

That is because changing colour does not force a recalculation.


My question is - "Can I set Excel to automatically upate each cell when anything is changed?" or "Is it possible to add a button the will run all formula on a page?"

First question, no, it doesn't happen. There is alraedy a button, or at least a key combination, Alt-F9 which will force a recalculation.

What I do is to either do a calculation based upon the criteria for setting the colour, thereby being indifferent to the colour, or add a toolbar button for setting the cell colour which forces a sheet recalculation.

.

[Parlablane]

xld,

Thank you for you reply here. I've decided that for the sake of speed to use the ALT + F9 combination to force the recalc.

I always thought that it was F9 on it's own, which explains why I could never get it to work!!:saywhat: :saywhat:

Thank you again for your input. It's most appreciated.

[Bob Phillips]

I always thought that it was F9 on it's own, which explains why I could never get it to work!!

F9 will force volatile functions to recalclate, Alt-F9 will do them all. You could add Application.Volatile to your code, but it is best not to if you can avoid it.