Loss of conditional formatting rules order when copying a sheet

[dronka]

I have a sheet in a workbook with 15 conditional formatting rules. When I copy the sheet within the same workbook, the copied sheet only has all of the rules, but they'be been re-ordered. Thoughts on how to fix this?

Thanks,
David

 

[Luke M]

I've run into this annoyance myself recently. Looks like it first started popping up with XL 2007. Hoping that it got fixed in 2010...till then, perhaps this can help?
http://www.mrexcel.com/forum/excel-...ing-rule-precedence-copy-paste-sheet-bug.html

 

[dronka]

Luke - Thanks for posting this! Yes, it's a clunky solution, but if it works, it works. Thanks!

David

 

[dronka]

Hmmm... Just tried to add "a1" to the beginning of each of my conditional formatting rules. Did that, then copied the sheet. The rules got all mixed up, just like before. Bummer, but it didn't work.

 

[bobhc]

Good day dronka

How are you copying the sheets, by sheet selection or by tab copy?

 

[dronka]

I'm copying sheets by right-clicking on the tab and selecting Move or Copy.

 

[Luke M]

Is there someway you could re-write the rules so that the order they are evaluated in no longer matters? I know that's not really what you want, but this is an annoying bug.

 

[dronka]

That might be possible by adding more complex functions to the rules, but it would take a lot of reprogramming, and I'm not terribly facile at using functions within conditional formatting. The spreadsheet's functionality really does depend on their order. Lame that this is a bug that hasn't been fixed. Thanks everyone for trying to help out!

 

[Luke M]

Lame that this is a bug that hasn't been fixed.

Microsoft's way of fixing it was to create a new version that you should buy.
But agree, it's very annoying. I checked back on the tool I built, and I had to change all the formulas to be order-independent.

 

[dronka]

Thanks, Luke.