Delete duplicated conditional formating rules

[lastminuteproject]

Hi all,

I've been updating a quite big excel sheet with the info for my work.

One of the columns contains the name of a mobile operator and with conditional formatting I was applying the correspondent color. Then those names were copied across other sheets. So, in order to keep the coloring fine I was just adding another rule with it's color for each new operator that I was adding.

PROBLEM

Then I was copying the format of that cell and aplying to all the other ones. What I dind't knew is that it was adding again the already existing rules!!

Example: First week I have 2 rules in the sheet. I add another rule for a new operator and I copy&paste the format to the other cells were the name of the operator can appear. Then I have the 2 existing rules plus 2+1 rules more (2 old ones and 1 new one).

So I end up with thousands of rules and now excel even crash if I try to modify any of them.

QUESTION

There is any possibility to edit the conditional formating rules in a more powerful way than the manager in Excel? there is any macro that delete duplicate conditional formating rules?

It's important for me since the whole excel sheet became really unstable and is the basis of my work.

Thank you in advance

 

[lastminuteproject]

I've been searching and you can list the conditional formatting with a macro and delete them all.

What I didn't found is how to delete only the duplicated ones.

I'm not good with macros so any help will be really welcome.

A short example code to delete all the conditional formatting is enclosed



Sub Macro1() For Each ws In ThisWorkbook.Sheets ws.Cells.FormatConditions.Delete Next End Sub</pre>

 

[lastminuteproject]

Looks like is known problem in Excel2007 that is fixed in Excel2010.

There is a hotfix here: http://support.microsoft.com/kb/973823

However, this fix the issue for future copy&paste of CF, but not for the already duplicated ones that I have

 

[lastminuteproject]

Looks like deleting duplicate Conditional formats is not an easy stuff.

At the end what I did is to download the add-on "FormatsAndStyles" from Jim Cone: http://www.mediafire.com/PrimitiveSoftware#0s8g1059rf04r,1

Which allow me to delete X conditional formats. I lost some of them with this, but at least the list was hand-able again (before had 75k rules).

I post it here so maybe some one else is interested.

Best Regards