locking bar chart colors in pivot tables

[Guest]

I use pivot charts frequently, and change the data displayed frequently. I
would like to "lock" my stacked bar chart colors so that my largest data
segment in the legend always displays blue in my stacked bars, my second
largest data segment always displays red, etc.

 

[Jon Peltier]

As Debra Dalgleish points out, loss of formatting is a known problem with pivot
charts. There's information in the following MSKB article, suggests recording a
macro as you apply the formatting:

XL2000: Changing a PivotChart Removes Series Formatting
http://support.microsoft.com/?id=215904

To apply colors based on values, you could use a macro like this to step through the
points:

http://www.google.com/groups?hl=en&lr=&[email protected]

or you could set up a conditional chart, using appropriate formulas for your conditions:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______