Self-Duplicating Conditional Formatting?

Question

I have a worksheet that has several regions with conditional formatting, maybe ten rules in all.  I noticed lately that it was running very slowly.  I checked the conditional formatting rules ("Manage Rules") and found that there were literally dozens, maybe hundreds of rules in the list.  Since deleting them one-by-one is extremely tedious, I decide to create a macro by recording the deletion of the first rule in the list.  The recorded macro really surprised me:

    Cells.FormatConditions.Delete  ' This is the one rule that I deleted???

    Range("F4:F23").Select                  ' Excel recorded the re-creation of all the rules!!!

    Range("B4").Activate

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

        "=IF($E5<> $F4,IF($E5="""",0,1),0)=1"

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

    With Selection.FormatConditions(1).Font

        .Bold = True

        .Italic = False

        .ColorIndex = 3

        .TintAndShade = 0

    End With

    Selection.FormatConditions(1).StopIfTrue = True

    Range("E5:E23").Select

    Range("B4").Activate

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

        "=IF($E4<> $F3,IF($E4="""",0,1),0)=1"

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

    With Selection.FormatConditions(1).Font

        .Bold = True

        .Italic = False

        .ColorIndex = 3

        .TintAndShade = 0

    End With

    Selection.FormatConditions(1).StopIfTrue = True

    Range("B4:D23").Select

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

        "=IF(LEFT($B4,1)=""-"",1,0)=1"

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

    With Selection.FormatConditions(1).Font

        .Bold = True

        .Italic = False

        .ColorIndex = 5

        .TintAndShade = 0

    End With

    Selection.FormatConditions(1).StopIfTrue = True

etc, etc, etc (hundreds of lines of code were created).

Two questions:

1.  Why are my conditional formatting rules duplicating themselves so many times?

2.  How can I delete them without clearing out all my other formatting as well?

Thanks,

Eric

Excel 2007 SP2

Windows XP SP3

Answer 1

Hi Eric,

Would you possibly be able to share the macro code that you created to remove the duplicates?  I've come across the same issue with rules duplicating dozens of times.  I'll definitely follow the process in the future to remove rules before copying any sheets over.

Thanks!

Terry

Answer 2

I also modified the worksheet code (which does a lot of copying and pasting, hence the duplicates) to delete duplicate CF rules on the fly. I had not heard of the bug you mention, Peter, but now it all makes sense.

In future it shouldn't be necessary to remove duplicates. As I mentioned before, simply remove all CFs in the destination range before you do the copy (or clear formats).

Regards,

Peter Thornton

Answer 3

I ended up writing YAM (yet another macro) which allows me to selectively delete a subset of the conditional formatting rules, based on chosen criteria.  My favorite criterion is "delete all duplicates".  It deleted 253 duplicate rules on the worksheet referenced above.  I also have "delete all overlaps (retain first rule in range)" to create exclusive CFs.  There are others, and I'll probably add more as I go along.

I also modified the worksheet code (which does a lot of copying and pasting, hence the duplicates) to delete duplicate CF rules on the fly.  I had not heard of the bug you mention, Peter, but now it all makes sense.

Thanks to both of you for your inputs,

Eric

Answer 4

1. Why are my conditional formatting rules duplicating themselves so many times?
2. How can I delete them without clearing out all my other formatting as well?

I filed this as a bug over a year ago. In effect what happens is when you copy CFs to a destination range any existing CFs in the destination are retained rather than replaced. If the process is repeated a few times it's easy to end up with a large number of unwanted CF rules. This is what I proposed at the time to avert the problem -

"Workaround is to 'Clear Rules' or 'Clear Formats' in the destination before initiating Copy in the source range."

That answers your Q1. There's no simple answer for your Q2. It might be simpler to remove all and start afresh, though no doubt you'll be able to determine the best approach with your knowledge of the workbook.

Regards,

Peter Thornton

Answer 5

To delete the rules  in sheet ->press ctrl+a -> home conditional formatting->clear rules->clear rules for entire sheet. This removes only remove conditional formatting and not other formatting such as fonts.

Since the conditional formatting rules are duplicating themselves it could be a case of corrupt workbook.

Try to repair the worksheet and check if it helps.

http://office.microsoft.com/en-us/excel-help/repairing-a-corrupted-workbook-HA010097017.aspx

---

Ruchi Bisht