Conditional Format breaks down into hundreds of separate rules.

[Hwatsonnn]

When I start my sheet off every year it begins with a rule:

=$K1="✓" Applies to: =$A:$O


As I enter information down that range between A and O, I post headings which split up the data.
Every time I post these headings, it breaks the CF up into separate rules. Having all of these CF's is slowing down my worksheet, which I confirmed by deleting all the CF's and giving it a go.
Is there anyway I can stop it from doing this?
Thanks for any help

 

[Jeffrey Mahoney]

That is one of the more annoying issues with Excel that I've been dealing with for a long time. CF cells get propagated everywhere. I want just one main range, but I end up getting hundreds.

For a couple of my projects I created a macro that "healed" the CF ranges. Because it was an unchanging environment, this could be done. If a person added a CF, the macro would erase that new one. I couldn't account for new cf ranges because of the way that VBA managed CF ranges. It was like I had to go find the built in CF ranges and save the new CF ranges also.

 

[Hwatsonnn]

That is one of the more annoying issues with Excel that I've been dealing with for a long time. CF cells get propagated everywhere. I want just one main range, but I end up getting hundreds.

For a couple of my projects I created a macro that "healed" the CF ranges. Because it was an unchanging environment, this could be done. If a person added a CF, the macro would erase that new one. I couldn't account for new cf ranges because of the way that VBA managed CF ranges. It was like I had to go find the built in CF ranges and save the new CF ranges also.

It's really got me thinking as it has put my work on the document to a complete stand still, I think I will have a go at creating a macro that heals them!
Off to do some research now.

 

[sparky2205]

Conditional Formatting fragmentation in Excel seems to be a problem that has been around for a long time.
If you google it you will find numerous discussions explaining how and why it happens. Alas without a definitive resolution as far as I can see.
Folk far better with Excel than me have struggled with it.
If you are going to do some research then this link might be a good place to start.

Excel conditional formatting fragmentation

Often, I create a sheet with conditional formatting, and set the cell ranges so that the conditional formatting rules are only applied once to a range of cells, e.g. Make $A$1:$A$30 red and Make ...

superuser.com

Have a look at the links within the post as well as the post itself.
And if you should happen across a solution or develop one yourself please be so kind as to post it here on the forum with flashing lights and ringing bells.

 

[Jeffrey Mahoney]

I remember now. In some of my past projects I created a hidden row where I kept all my formulas and CFs. The macro simply copied the formatting and formulas from the hidden row to all the table rows. Now with Excel Tables, I'm not sure how that would work. Using the first row in the table could work as well.