Macro will not allow conditional formatting with a user-defined function

Sorry. This is not quite as clean as I need. The mandatory "recalculation" method does not work very well (on some sheets, I have to enter multiple numbers multiple times for all cells to fully recalculate and show the conditional formatting results -- F9 does not seem to work at all, on this setup). Also, this means that the conditional formatting becomes a permanent part of each tab in the worksheet. This takes up space, and slows navigation through the worksheet (it is a very very large spreadsheet).

I need a way to have the conditional formatting turned on for just a brief time, and then remove the formatting entirely.

Again, I can set it up manually, and it works great. And I can have a macro to remove it, and that works great. I just need a macro to do the initial set-up.

Getting closer, but still not what I need. I thought I could get a solution without going into too many details, but here is the complicated full story.... This is a large spreadsheet, with thousands of rows and 100+ columns, doing a "Retrieve" from a Hyperion Essbase database cube. The left-most few columns, and the top-most few rows, contain criteria for the Retrieve. The sheet also has many formulas throughout, for sub-totaling and totaling various points of data, both on many rows, and in some columns. The columns are typically months of data, across several years, grouped in sections of columns such as Budget, Actual, and Forecast data, with calculated quarterly break-points. We want the users (Cost Center Managers, etc.) to look at the data as retrieved (actual through certain periods, and forecast and budget for other periods), and then over-ride the forecast cells with new projections.

This is where it gets tricky. They can either enter a plain number, or a formula to represent their answer. Once they are ready, this data can be up-loaded back into Hyperion Essbase, as-is, into a new Essbase cube. If the previous forecast was loaded into cube Forecast1, for example, then that was retrieved, and the new forecast will be up-loaded into cube Forecast2. Then, when we are ready for the next forecast, we can re-retrieve this sheet, pulling new Actual data, and the latest forecast (now from cube Forecast2), and ask the Managers to provide the next forecast, which will now be loaded into cube Forecast3.

The problem, is that Hyperion Essbase will over-write (on a Retrieve) any number value with a new number from its cube. But, it will NOT over-write a formula. So if a user has entered a formula for their forecast (such as 975,000/6), then that cell will be ignored and not retrieved. So, if we had just forecast August, and someone forecast a cell by using a formula, and now four weeks later, we retrieve August actual data, then that particular cell will not be retrieved with August actual data. Our balances will be wrong. We need a way to quickly spot these items, delete the formulas, and re-retrieve the data.

Conditional formatting works exactly the way we want it to work. It high-lights all formulas, even those that we created, and then the conditional formatting can be easily removed when we are finished, without affecting any previous formatting then in place. This is important because we have already formatted our own formulas, so that users know not to touch those items, and also so that sub-totals can easily be spotted, and we do not want the formatting of those formulas changed. The last suggestion posted would delete that formatting.

So, in summary, we already have formatting in place throughout this workbook, and do not want that changed. Only conditional formatting can be applied and then removed without disturbing that original formatting. We just need a way to automatically and quickly apply the CF and then remove it, without going through all of the steps one would normally have to do for a CF. A macro seemed like the best way, if it could be done.

The problem there is that the data entry cells are interspersed among hundreds of legitimate formulas; meaning that there would be hundreds of ranges to "value." Also, the ranges would be different each month. This month, people are forecasting September through December. Next month, September turns to Actual, and only October through December are forecast, so September needs to be "cleaned." The following month, October needs to be "cleaned." etc. The code to clean every range would be enormous.