Conditional formatting rule precedence, copy paste sheet, bug?

orbsplateau

New Member
Joined
Dec 3, 2010
Messages
2
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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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?
 
Upvote 0
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.
 
Upvote 0
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-com:office:office" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
clunky, but works. Using this system, when I copy to a new sheet, the order remains as the original sheet.<o:p></o:p>
 
Upvote 0
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
clunky, but works. Using this system, when I copy to a new sheet, the order remains as the original sheet.<o:p></o:p>

Excellent, thanks for that - it's worked a treat! :)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top