Forum Discussion
Conditional Formatting
1. How big is the range you are applying the conditional formatting to?
2. Do you have calculation set to Automatic or Manual? (Formulas tab > Calculation Options)
3. What do your rules look like? How many? What are the Formulas?
I may be able to get the same effect using a worksheet change event....
I can find some old threads referencing a conditional formatting bug but it looks like the support article has been removed (dead link):
https://www.mrexcel.com/forum/redirect-to/?redirect=http%3A%2F%2Fsupport.microsoft.com%2Fkb%2F213243
1. How big is the range you are applying the conditional formatting to?
That's interesting...when I choose a column, I select the WHOLE column, so that would be like row =1 to row = infinity (or last row on the spreadsheet). Perhaps that is overwhelming things.
2. Calculation is set to "automatic"
3. See Attachment for a typical rule. Nothing complex. The other columns may differ somewhat, but nothing difficult.
Reid-
Maybe try reducing the conditional formatting range to something a little smaller. Not sure how long the sheet is or how often / fast the rows grow in it, but if you reduce it to something smaller than 1,048,576 it should help with performance a bit.
- I tried that. I made the range something like 10 cells, but the problem persists. Apply the Conditional Formatting rules and you don't see the results until you Page up then Page down to your 10 cells. I should add that you don't HAVE to Page Up/Down to force the cells to highlight. Cursing up and down accomplishes the same thing. The idea is that you have to slide the view of the cells until they are no longer in sight, then back again. So you can slide the viewpoint sideways, not vertically to force the Conditional Formatting to work.
I just got accustomed to having to play around with my spreadsheet every time I open it if I want to actually see the Conditional Formatting of those 3 columns. Usually, once they highlight, they stay highlighted until I close the spreadsheet. Although a few times they disappear for no apparent reason while the spreadsheet is still open and by that I mean the Formatting doesn't display, not that the cells disappear.I think the issue here ends up being one of "Formatting Overload". My particular worksheet is thick in color formatting. I have many different columns with different "fill" colors (not conditionally formatted, just background colors to distinguish types of columns). So when I added 3 more columns (farthest right, see attachment) and applied Conditional Formatting to those columns, Excel must be overtaxed to present the results to me spontaneously. I have to "refresh" the page in order to see the results.
As suggested proof of this, I opened a new worksheet with nothing but a column of values and applied Conditional Formatting to them with instantaneous results. So it does work. But not when this column of values is inserted into my active worksheet. I have to refresh the page in order to see the formatting.
