How to control Bar colors for Pivot Chart

[Dee]

Hello, I created a Pivot Chart (3-D Clustered Bar), it works fine except when
I pivot the bars change colors, and the a white background appears on the
data points. How can I make it so that the bars keep their color and their
is no background in data on data points. Others will have to use this chart.
i'm open to using VBA.

 

[ShaneDevenshire]

Hi,

Are you using 2003 or earlier? If so Excel's pivot charts loose their
custom colors when you refresh, tha chart or the pivot table. There is a
workaround for this - choose Tools, Options, Color, and note the color
palette at the bottom of the screen applies to charts. Change the colors
here and they will remain fixed on the chart. These changes affect the
entire file. So with this approach it is not possible to have two different
charts whose first bar colors are different and retained.

All formatting of the data points are lost during refresh. You have two
solutions for this - 1. Upgrade to 2007, 2. Use VBA.

Although I haven't done it I suspect that you should attach the vba code you
want to use to the

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'Your code here
End Sub

You can record the formatting you want to apply to a chart and then move it
into the subroutine above. This subroutine is entered in the Sheet1 object,
where Sheet1 is the sheet with the pivot table.

If this helps, please click the Yes button.

 

[Dee]

Thanks Shane, I do use 2007 so it works well for me, but I have 2003 users
of my file and it changes for them.
Is there anyone out there that knows tell me how to code solution using vba?
I really need to complete this.