# Unintended Proliferation of Conditional Formatting Rules

#### expos4ever

##### New Member
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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### oldbrewer

##### Well-known Member
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 ?

#### Gerald Higgins

##### Well-known Member
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.

#### expos4ever

##### New Member
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 ?

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:

#### expos4ever

##### New Member
Hello Gerald: Thanks. The problem is that the number of unwanted rules is HUGE and it takes an eternity to delete them.

#### oldbrewer

##### Well-known Member
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

#### expos4ever

##### New Member
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.

#### Fazza

##### MrExcel MVP
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

#### expos4ever

##### New Member
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.

Replies
3
Views
231
Replies
4
Views
311
Replies
1
Views
155
Replies
1
Views
320
Replies
3
Views
106

1,191,353
Messages
5,986,166
Members
440,008
Latest member
Cmbuck

### 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.

### Which adblocker are you using?

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

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