Conditional formatting rule precedence, copy paste sheet, bug?

[orbsplateau]

version:
Excel 2007 (12.0.4518.1014)
Windows 7 Ultimate, 32-bit


Hi,

I have a worksheet with 7 conditional formatting rules. Some use relative, others use absolute references; 2 of them overlap.

When I duplicate the worksheet however, the rule precedence order gets mixed up. Copying all the cells and pasting the formatting to the new sheet doesn’t help either. I end-up with duplicate conditional formatting rules in the wrong order.

Moving the sheet to a new file/worsheet has the same effect.

I created a new sheet and re-entered the conditional formatting rules manually. By default, the newest rule applies first. I did not change the default order, but again, when I copied or duplicated the sheet, the order changed.

I tried creating a macro to do the copying and pasting, but again, the rule order changes.


Has anyone else encountered this bug? Is there something that I’m missing?
I have to duplicate this sheet once or twice a day (it’s a template). Re-sorting the conditional formatting rules every time is getting very annoying.

Any ideas?

 

[StuLux]

I personally think there are many bugs in 2007 additional conditional formattin functionality and it does not surprise me that you are encountering difficulties. I have not encountered this one specifically but if there is no solution then I would suggest recoding a macro of all the steps you have to take to "fix" the new sheet after copying and then run this each time you need to duplicate the sheet. An alternative would be to create a new workbook by renaming the existing one rather than a new worksheet?

 

[orbsplateau]

StuLux,

have you had a better experience with Excel 2010?

 

[StuLux]

Must admit that I haven't had much of a chance to use it so couldn't tell you, there are some interesting and useful additions in 2010 such as inbuilt sparklines but I really haven't had an opportunity to use.

 

[ErikHP]

I realize these are older posts, but as I found them when I had this problem, I wanted to share an answer for any later readers...........

AS per another post on this -----

I have found that when you copy the new sheet, the formatting rules apply based upon the cell address. In other words, if you have formating starting at a1 it will reorder to the top of the rules if all other formats start with later cell addresses.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
To get around this I have been inculding a1 in conditional formats even if i don't need it. like this a1,$c$13:$t$88<o></o>
<o></o>
clunky, but works. Using this system, when I copy to a new sheet, the order remains as the original sheet.<o></o>

 

[Mr Statto]

I realize these are older posts, but as I found them when I had this problem, I wanted to share an answer for any later readers...........

AS per another post on this -----

I have found that when you copy the new sheet, the formatting rules apply based upon the cell address. In other words, if you have formating starting at a1 it will reorder to the top of the rules if all other formats start with later cell addresses.<o></o>
<o></o>
To get around this I have been inculding a1 in conditional formats even if i don't need it. like this a1,$c$13:$t$88<o></o>
<o></o>
clunky, but works. Using this system, when I copy to a new sheet, the order remains as the original sheet.<o></o>

Excellent, thanks for that - it's worked a treat!