Copying Conditional Formatting changes rule order etc.

Question

Hello All,

I am using Excel 2007 and Windows XP.

I use conditional formatting in quite a few cells in my spreadsheet to change background and font colours.

Below is an example of the 5 formulas used in CELL H176.

The Order is also as shown in this example:

1. =($G$6=170)
2. =IF(K176="",H176<>"")
3. =IF(S176="",H176<>"")
4. =MOD(ROW(),2)=1
5. =MOD(ROW(),2)=0

I use "FORMAT PAINTER" to copy the formatting into other cells.

Example cells H177 to cells H181.

What happens is, firstly, the order of the conditional formatting rules is changed to as follows:

4)

5)

1)

2)

3)

Secondly, the first formula should change so as the number 170 in**$G$6=170**is incremented by one for each higher cell it is pasted into.

This does not happen. The number always stays at 170.

If I remove the dollar signs from the formula, G6 changes to G7, G8 etc.

G6 must always remain G6 no matter which cell number I copy it to.The Values of Formulas 2 & 3 change as necessary, no problem.I also have 3 conditional formats in each of cells B7, C7 and D7              B7                                         C7                                              D71) =(B7+21<TODAY())      =(C7+21<TODAY())      =(D7+21<TODAY())2) =MOD(ROW(),2)=1       =MOD(ROW(),2)=1        =MOD(ROW(),2)=13) =MOD(ROW(),2)=0       =MOD(ROW(),2)=0        =MOD(ROW(),2)=0 When I copy these to e.g. cells B8, C8 and D8 the order of the conditional formats also change as follows:The 3 formats from Cell B7 change from 1,2,3 to2,3,1 when copied toB8The 3 formats from Cell C7 change from 1,2,3 to2,1,3 when copied toC8The 3 formats from Cell D7 DO NOT change order. They remain as 1,2,3 when copied to C8I seems they behave differently when I copy to colums as to when I copy across in rows.I have tried everything I can think of. The only option I have at present is to go into each cell's "Conditional Rules Manager" and Change the orders and values manually. This is not only time consuming but utterly boring and disheartening.Last but not least, If a cell has 3 rules in it and I paste the whole five rules from another cell, the existing rules are duplicated so that instead of 5 rules I end up with 8 rules although 3 of them are duplicates!If anyone can tell me how to overcome these problems they would surely save my sanity.Thanks in advanceMaltese Falcon

Answer 1

'Stop if True' appears to have no effect on the problem of the order changing. I'm also having this problem. After selecting 12 cells in one row of the pivot, I use Format Painter to drop that format into the corresponding 12 cells below the selected row--and the order of the formatting rules changes everytime I do this, and always to the same incorrect order. This does not happen in another pivot table where I've done the same thing.

Answer 2

When I posted my original reply, I hadn't actually tested using the format painter to copy conditional formatting. I have now, and I can't reproduce the problem.

Whichever way I use the format painter, the order of the rules is preserved. Is your Excel 2007 up-to-date with updates?

You could, however, try the following:

Select the cell with the correct setup.

Click Conditional Formatting > Manage Rules...

Edit the Applies To box to expand the range the rules apply to (when you've edited one, you can copy/paste the range into the rest)

Answer 3

Hello Hans,

Thanks for your reply. I changed the formula from ($G$6=170) to**($G$6=A176),with A176 being the cell that contains the value170**. It works!

Regarding the problem with the conditional formatting rules changing order when I copy and paste using Format Painter, do you mean that if I have 5 rules and put them in a specific order and then tick all their "Stop if True" boxes the order will not change when I copy and paste them with Format Painter?

Regards,

Maltese Falcon

Answer 4

In Excel 2003 and before, the processing of the conditional formatting rules stopped at the first condition that evaluated to TRUE. In Excel 2007 and later,all rules are processed until one is encountered that is TRUE and that has "Stop If True" set. The "Stop If True" check box has been added for compatibility reasons.

If you want the number 170 in the condition =($G$6=170) to be adjusted automatically, change the condition to**=($G$6=(ROW()-6))**