Format painting bars in a chart

[PatK]

I have a funky problem..not sure why. Let's say I create a bar chart, and
format each of the bars with a nice set of gradient colors, that varies based
upon the data series source. In other words, several different color bars,
on standard color per series.

Now, if I happen to "touch" the data behind the chart in any way (in my
case, a pivot table), the data element I change seems to go back to a
"default" color scheme. I still have other bars that look like I want, but
say, one month bar, for one data series, now looks different than the other
11 for the year. I want to to to "paint" the format from the good bars, back
onto the mucked up bar.

Any way you can think of to make this work?

 

[Jon Peltier]

Pivot charts are well known for resetting their formatting when the pivot
table is adjusted. It's a known issue, and Microsoft recommends recording a
macro when you reformat the chart, so you can rerun the macro as required.
Alternatively you could create a custom chart type and reapply it as needed:

http://peltiertech.com/Excel/ChartsHowTo/CreateCustomTypes.html

or make a regular chart from a pivot chart:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=553

Also, I'd dispense with the fancy formatting, but I'm a voice in the
marketing wilderness.

- Jon

 

[PatK]

Thanks, Jon...I will give it a shot. I must say, I am finding more
"oddities" and bugs with the 2007 suite, than I had hoped. Oh well...I guess
there is no looking back....

 

[Jon Peltier]

The only real use I have for 2007 is to answer questions, both here and for
my clients. I still do my development in 2000-2003, and for my own use I
stick to 2003.

- Jon