Conditional Formatting - Active Sheet

Conditional formatting can provide for nicely coloured presentations but I notice the formatting is executed on all worksheets, not just the active one. This wastes a lot of processing time.

What is the best way to inhibit calculation on non-active and hidden sheets?

My thought is to enclose each formatting function in a kind of IFACTIVE( ) function, but that would require a range parameter such as the $A$1 cell of the given sheet so that the function could return, say, (A1cell.Worksheet is ActiveSheet). Would that even save time? Is there a smarter, easier way? Is there a common solution that others are using?

Is there a practical reason that you are concerned about this? Are you experiencing lags that you are attributing to conditional formatting? I would be curious as to how you determined this. I have never considered it so have never set up a test.

I would expect that conditional formatting rules are calculated the same way that other worksheet functions are calculated. Excel maintains a calculation chain so that only those formulas affected by changes to other cells are recalculated. I would expect the same thing happens for conditional formatting rules. Therefore it would be pretty efficient. The exception would be if you are using volatile functions in your conditional formatting rules, in which case all hell breaks loose.

Whether you consider this a waste of processing time is a computer science type of question. If the rule calculation is not performed on a sheet at the time the rule is affected by changes to its references, then all rules would have to have a "calculate me" flag and be calculated when the user does activate the sheet, creating lag time at that point. It's a question of eager calculation vs. lazy calculation, and there is no single right way to do this. It's a design tradeoff that the software engineer makes.

Your proposed solution of an IFACTIVE function, which would be a VBA UDF, would be self-defeating because invoking a VBA function to test whether a sheet is active would probably take longer than just calculating the conditional formatting rule.

I have never seen a solution for this (in over 25 years) because I've never heard anyone say it was a problem.

Thanks Jeff. I have also been working with Excel for over 25 years and it is the first time I have encountered it.

To answer your question, no, I am not using volatile functions but the reason I ask is that I have had an Excel message pop up saying:
SLOW WORKBOOK? 99% of your workbook has unused formatting and metadata that can be optimized to improve performance.

Never mind; I shall persist, as I always do. I just thought the community might know something about it.

Thanks anyway.

I'm only one guy and it hasn't been long since you posted so others may have more to contribute.

I get those performance alerts and when I've followed up I usually find them fairly worthless, things like formatting in unused cells that don't noticeably affect performance.

I do not know why CF rules would be volatile. That bears further research.

I haven't seen performance impact from applying CF to an entire row or column except when it uses an array formula. Excel seems smart enough to evaluate CF formulas only within the used range, but not smart enough when it's an array formula. So for a column it will evaluate over a million cells. Given the information in your link, perhaps it could be problem to use icons for an entire column, however. As I said, it bears further research.

I have done some preliminary experiments on this and have so far concluded that:

1. Conditional formatting rules that use a formula are not volatile. They are updated only where there is a calculation that affects a conditionally formatted cell on the active sheet.
2. If a cell on a non-active sheet is affected by a change on the active sheet, the conditional formatting is not calculated until the non-active becomes active.

The one surprise that I cannot explain is that if a range is conditionally formatted, and the value of a single cell in that range is changed, the rule for every cell in the range is recalculated. In fact, in my test it was calculated four times.

Will see if it's possible to do similar tests on other types of rules.

Methodology

Put random values into the range A1:C3
Add a UDF that will return the value of a cell and print the cell address and its value to the immediate window

Please Login or Register  to view this content.

Add a conditional formatting rule for A1:C3:

Formula:

Please Login or Register  to view this content.

So every time a CF rule is evaluated, a line it written to the Immediate window telling me which sheet, which cell, and what its value is.

That's an interesting test and I confirmed that if you use OFFSET in a worksheet formula, it will indeed ask you to save if all you do is open and close the file. That surprises me because my understanding is that a volatile function is recalculated any time there is anything recalculated. But I don't why there would be any recalculation at all simply from opening the workbook.

Nonetheless, I agree that CF rules are not volatile, however, I cannot explain why a rule for an entire range would be reevaluated when only one cell in that range changed.