Unintended Proliferation of Conditional Formatting Rules

expos4ever

New Member
Joined
Jun 26, 2015
Messages
38
Many conditional formatting rules appear that I never explicitly defined. Here is the general pattern of what happens:

1. I define a conditional formatting rule and apply it to a range of cells (generally, a finite, contiguous block of cells in a single column). These rules govern the colour of the cell "fill". For example, I may have a rule that instructs Excel to use red fill for every cell in the first 100 rows of column A that contains the phrase "invalid requirement".

2. Later, I find many versions of that same rule have mysteriously appeared with the property that they apply to a sub-set of the cells I had initially defined as the range for the rule. So, with reference to the example in item 1 above, a rule that turns cell A73 red if it contains the phrase "invalid requirement" appears even though I never defined such a rule but instead defined a rule that should apply to cells A1 through A100 inclusive.

The result is a nightmarish explosion of unwanted rules that apply to individual cells.

I have a theory as to why this is happening: I sometimes copy the contents of a cell to which the rule I initially defined applies and paste it to another cell in that same range. I bet that produces an extra rule.

Any guidelines on how to avoid the unintended generation of rules would be appreciated.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
if cell D1 is red because of rule 1 and you paste it to say A1:A10 then that rule applies also to A1:A10

the same cells can have many different rules producing different colors.

so why are you surprised that say A7 has that rule ?
 
Upvote 0
expos4ever - I know what you mean, I get this myself.

I'm afraid I don't know a solution, it seems to be a feature of newer versions of Excel.
Older versions didn't seem to be as bad for this kind of thing.

What I do is, I have a couple of worksheets that I know are bad for this kind of thing, and from time to time I go into them and manually tidy up the CF rules.
I wish there was a better solution, and I'd be delighted to hear of one.
 
Upvote 0
if cell D1 is red because of rule 1 and you paste it to say A1:A10 then that rule applies also to A1:A10

the same cells can have many different rules producing different colors.

so why are you surprised that say A7 has that rule ?
Thanks for your reply.

I assume you meant A73, and not A7. Perhaps I was not clear: I never paste from one column to another column - I only paste within columns. So why do I get such a proliferation of rules?
 
Last edited:
Upvote 0
Hello Gerald: Thanks. The problem is that the number of unwanted rules is HUGE and it takes an eternity to delete them.
 
Upvote 0
I cannot reproduce your problem (I have excel 2000)

if A3 is 3 and you paste to A6 A6 becomes 3 and the rule that turns cell red if contents = 3 comes into play
 
Upvote 0
I cannot reproduce your problem (I have excel 2000)

if A3 is 3 and you paste to A6 A6 becomes 3 and the rule that turns cell red if contents = 3 comes into play
Thanks. I understand - I cannot intentionally re-produce my problem either. But one thing is certain: New rules are being automatically generated and I have no idea why this is happening.
 
Upvote 0
At one of the Excel revisions this was (inadvertently, I assume) introduced. I think new to Excel 2007.

It stuffed up some spreadsheets I use at home. For me the solution was to programmatically, that is via VBA, (1) delete the conditional formatting on sheet deactivation, (2) and apply it on sheet activation.

cheers
 
Upvote 0
At one of the Excel revisions this was (inadvertently, I assume) introduced. I think new to Excel 2007.

It stuffed up some spreadsheets I use at home. For me the solution was to programmatically, that is via VBA, (1) delete the conditional formatting on sheet deactivation, (2) and apply it on sheet activation.

cheers
I appreciate your suggestion. The problem seems to have disappeared for no apparent reason (obviously, that's suspicious), but I will follow your suggestion should the problem recur.
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top