Loss of conditional formatting rules order when table updates

[Chanzaiming]

This is driving me crazy!!! Please tell me it's not a bug and there's a solution to this...

I noticed that my conditional formatting rules reorders every time the table is updated
specifically when:
1. you delete a row, either table row or sheet row
2. you insert table rows

But, the order stays the same when
1.you insert sheet rows;
2. it's not in a table(you can try doing the same thing in the test sheet)

For example, in the attached workbook
tab "Sample", watch cell K4 & L4.
The red bold format should be the last to run.
After you move red bold format rule to the bottom and save, K4 will be green fill with red bold text, and L4 will be blue fill with red bold text.
however, if you delete any other table rows, you will see that the formatting changes due to the order of the rule changed.

However, the issue does not present if you make the rule order correction in a non-table area. e.g. you can delete rows in the "test" tab, cell B7 and B9's format does not change.

ps. I did found these 2 posts, but I believe it's a different and crazier issue...

http://chandoo.org/forum/threads/lo...-order-when-copying-a-sheet.12516/#post-74027

http://www.mrexcel.com/forum/excel-...ing-rule-precedence-copy-paste-sheet-bug.html

 

[NARAYANK991]

Hi Yuming ,

I am not sure I have understood you ; can you give a specific example of the cells which change their formatting , when one or more specified rows are deleted ?

Have you tried checking the Stop if True boxes for each rule ?

Narayan

 

[Chanzaiming]

Hi Yuming ,

I am not sure I have understood you ; can you give a specific example of the cells which change their formatting , when one or more specified rows are deleted ?

Have you tried checking the Stop if True boxes for each rule ?

Narayan

tab "Sample", watch cell K4 & L4
move the red bold text rule to the bottom, save
then delete any row, e.g. row 5,
and you will see the format changes, if you go in conditional formatting manager, you will also see the red bold text rule go above the green fill again.

I don't use the Stop if True because I want it to have both Green fill and Red bold text formatting when applicable. and somehow it only does it when the red bold text rule is at the bottom.

hope it make sense. if not, i can do a couple screenshot later.