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

 

[Andrew Poulsom]

Welcome to the Board.

Did the target range already have conditional formatting before the paste? It seems that rules are appended rather than overwritten.

http://www.eggheadcafe.com/software/aspnet/31510819/format-painter-appends-co.aspx

 

[Urbs]

Many thanks for the response Andrew.

The cells I am pasting to are 'brand new cells' in a brand new workbook. They have no conditional formatting applied to them already.

I copy 29 rules, and 29 rules are pasted - just in a different order.

I did wonder if the pasted order was the order I wrote and applied them in, rather than the order I had changed them to be. But as I can't remember what order I wrote them in I can not be sure.

Cheers,

Urbs

 

[Andrew Poulsom]

That's a good theory. Why don't you test it with a couple of conditions?

 

[Urbs]

That's a good theory. Why don't you test it with a couple of conditions?

Thanks very much for the advice Andrew.

Just thought I would report back.

I created a 'simple' table with 6 Conditional Formats. Copying and pasting worked fine, if left in order created.

If I changed the order though, and then copied, the pasted formats reverted back to the order when created (obvioulsy the newer the created Condition the higher the priority)

It looks like the only way I'm going to get round this is write down all my conditions, their formats and to which cells they apply. Delete allthe conditional formats and then enther them in the reverse priority.

This way when I copy and paste the formats are copied and pasted with the same priority as I want them.

(I will be copying and pasting quite a few times, so the above will be easier than copying, pasting and then changing priorites)

Many Thanks for your help, I think I may find myself using this forum frequently.

Have Fun.

 

[Andrew Poulsom]

What happens if you copy the worksheet to a new workbook rather than the cells?

 

[Urbs]

What happens if you copy the worksheet to a new workbook rather than the cells?

Just tried it, and the proirities revert back to order in which created.

Having the extra Conditional Formats are great, not being able to copy and paste them in the priority you want is not so great!

What Microsoft give you with one hand, they take away with the other!

Have Fun,

Urbs

 

[cummingsea]

Try selecting PASTE SPECIAL, make sure ALL is selected, not sure this will help.

 

[Urbs]

Thanks cummingsea, but I only want the formats and none of the data from the cell, as I already have data in the cell I am pasting to.

However, I have just checked this on my 'dummy' file and it still reverts to originally written order.

Cheers.

 

[ErikHP]

Actually through trial and error I 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.

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

clunky, but works. Using this system, when I copy to a new sheet, the order remains as the original sheet.