Problem with conditional formatting and recalculation

[Mal B]

I have spreadsheet which has a lot of optional inputs and an output, after much calculating, via pivot tables. Some of the information in the pivot tables needs "de-linking" from the input during the process and to do that I use a bit of VBA to copy the relevant values from the pivot tables to other ranges.

Unfortunately it doesn't work. And the reason why it doesn't work is that the VBA code does not wait for the pivot tables to recalculate before going on its merry way. Normally I would test the state of the Application.CalculationState property to find out whether the recalculations were complete. But I can't - because of what I think is an Excel bug, the CalculationState already returns xlPending when the spreadsheet contains any volatile functions.

Now there are no volatile functions in the spreadsheet, BUT using conditional formatting also makes the spreadsheet volatile and that is what is causing the problem. I need conditional formatting to alert users as to what optional inputs they need to input by colour coding the cell backgrounds and borders so I can't remove all the conditional formatting.

So how do I either (a) stop the VBA code from executing whilst retaining conditional formatting or (b) make some form of conditional formatting non-volatile? I can't find any way of achieving (a). For (b) I can only think of putting all the formatting into code and executing it through the OnChange event - which is clunky, slow and tedious to implement.

Any suggestions welcome.

[tim201110]

"I have spreadsheet which has a lot..."
if only one could have just a quick look at it

[Mal B]

Sadly, I can't post it. Too confidential. But I think I have given enough information to show what the problem is. If not, do ask for clarification.