Conditional Format breaks down into hundreds of separate rules.

Hwatsonnn

Board Regular
Joined
Nov 14, 2018
Messages
57
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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 :)
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
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

Board Regular
Joined
Nov 14, 2018
Messages
57
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Active Member
Joined
Feb 6, 2013
Messages
293
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
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

Well-known Member
Joined
May 31, 2015
Messages
1,768
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,522
Messages
5,548,545
Members
410,848
Latest member
anuradhagrewal
Top