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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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