Keeping conditional formatting priorities when copying and pasting to another workbook

[Urbs]

Hello There,

I am trying to copy and paste conditional formatting from one excel 2007 workbook to another.

I have a line on a sheet where I have created 29 different rules, and have set the priorities accordingly.

I also want to use the same conditional formats on another workbook, but when ever I copy and paste (using 'paste special' - formats), whilst all the formats paste across to the relevant cells, the priority order of the formats changes.

The format I had moved to the top, now appears as 14th on the list. The formats in position 2, 3 and 4 are now 1, 2 and 3 respectively - and so on.

Many Thanks (in advance),

Urbs

 

[tigeram]

Two ways of doing this !!!

There are two ways to counter this problem. Start by clearing all rules from all target cells except the 'source cell' with the rules you want to roll out.
(Option-1) ... Restrict the 'Applies to' range on the source cell SPECIFICALLY to that cell THEN 'format paint' to the rest of the range
(Option-2) ... Restrict the 'Applies to' range on the source cell SPECIFICALLY to that cell THEN manually add the range-end to each rule in turn
Both these ways result in the correct precedence order being retained across all cells in the range.
To explain: In my example, my 'source definition cell' was E6, and I wanted the five rules I had coded against that to apply all the way down column E to cell E383.
(1) I set up the five rules in cell E6 and made sure that the 'Applies to' element related the rule SPECIFICALLY to that cell only.... [=$E$6]
I then made this the active cell, clicked the 'Format Painter' button and then dragged the format down the full list of cells in column E.
Reviewing the 'Applies to' element of ANY of the newly formatted cells now shows the rules as active between E7 & E383 - which is fine ... [=$E$7:$E$383]
(2) I set up the five rules in cell E6 and made sure that the 'Applies to' element related the rule SPECIFICALLY to that cell only.... [=$E$6]
I then went back into the Conditional Formatting dialogue on cell E6 and manually added the 'range-end' to the 'Applies to' element for each rule in turn.
Reviewing the 'Applies to' element of ANY of the newly formatted cells now shows the rules as active between E6 & E383 - which is fine ... [=$E$6:$E$383]
Both these methods worked in retaining the order of precedence in the rules, as set up in the 'source cell' E6, across all newly formatted cells.