The Conditional Formatting in Excel Pivot Table Disappears upon Refresh (Excel 2007, 2010)

Let's say you want to conditionally format your SharePoint exported pivot table (or any Excel table). In this example one column includes the classes A and B and another is a sub-class A1, A2, B1, B2 etc., and you want all class and subclasses show in different colors, in this case if the text in column A starts with A, or B.

 The 2 rules you need may look like this: (the range A5:F95 contains the pivot table)

And the specific rule uses the left 1 character of the cell in column A (take away the $ sign from the row # to let the reference be relative):

Now, if you refresh the table, it may look like this:

If you analyze again your rules if will find that they were changed. Why? I don't know:

The work around is just to select the cells in column A starting with A5 (in this example), and 'format copying them (the brush icon) the equivalent cells in B-F columns. You will get the colors back and a refresh will not blow away the colors in the data area.

Excel adds two new rules that apparently solve the issue: