Conditional Formatting duplicating rules

[Deleted]

Hi!

I recently encountered a very big problem with Conditional Formatting in Excel 2013.

Now, I don't know if this is fixed in later versions of Excel. I can not upgrade my version either, at least not until I receive admin access to my office computer.

So, here's the deal. I have a spreadsheets with a range of entries:

Now, let's apply a rule for Conditional Formatting. Gradient color scale based on value, simple stuff.

As you can see, the range goes all the way to B15. This is only to allow easy demonstration of the problem.

Now, let us see what happens when we copy the entire row of 6, and paste it below our number 9.

Seems all well and good, wouldn't you agree?

But wait a minute, why is it that the pasted number 5 is as yellow as the number 9 above?

Let's take a look at the Conditional Formatting Rules Manager;

Uh-oh! It seems Excel has duplicated our rule, and made the first one exclude the pasted row.

Why is this?

Does it have a solution?

We had a problem with this where our very important backlog file became completely unusable because of the same problem, and with more than 1000 rules for Conditional Formatting, this slows the file down by a lot. Also, to work with the file efficiently, we need to copy the entire rows since we use so many columns.

I know that copying the cell individually does not have the same effect. I am looking for other solutions.

[#2 · Accepted solution]

Yes that happens a lot with CF rules. What I tend to do is have a stashed-away row which has just the CF rules and formatting set up exactly right. To fix things I copy that row and paste-special, formats it on top of all "working" rows.

[#3]

Okay, seems like a good way around it.

Does this remove the leftover rule though, or is that still there?

[#4]

It should replace all rules in the area.

[#5]

Will need to try this properly on monday, if it works it should also make for a fairly simple VBA script, which would be great to minimize work. Thank you!

[#6]

Select Paste Values, or if you are using Ctrl+V, click the Paste Options button, and select the Values icon, and Excel won't copy any formatting to the destination.  

[#7]

This would also work, although we would need to paste formulas instead of values. I believe we have tried this and that the formatting rules didn't quite like it for some reason.

[#8]

There's another option in the Paste Options menu that will copy the formulas as well, without the formatting.  it's the third option if you right click, and in the full paste special menu, there is a formulas and number formatting option that should work for your purposes.