Pivot chart, macro to change color

[Daniel]

When I hit "refresh data", my chart go to default color. How can I avoid
this by writing a simple macro and assign to every worksheet?
Thnks
Daniel

 

[ShaneDevenshire]

Hi,

Without writing a macro you can change the color pallette - choose Tools,
Options, Color and in the Chart Fills and Chart Lines modify the colors to
the ones you want. These are the colors the charts use for default colors.

One issue is that a custom color pallette belongs to the active workbook not
to Excel.

1. You can copy a pallette from one file to another by opening both files
and going to the Tools, Options, Color tab and choosing the Copy colors from
dropdown or
2. You can change the color pallette in the default workbook and then all
future workbooks will inherit that pallette.

If you still need a macro solution let us know

 

[Daniel]

Thanks Shane,
I have a lot of pivot charts and can not manually configure every charts.
I also tried "custom chart" and saved with a name but it's not work.
I am interested in macro for me and co-workers who are not familiar
with excel.
The macro should be applied for every charts in the work book. I just need
an example and modify from there.
Thanks again
Daniel

 

[ShaneDevenshire]

Hi,

Here is a sample macro:

Sub ResetColorPallette()
ActiveWorkbook.Colors(17) = RGB(204, 0, 153)
ActiveWorkbook.Colors(18) = RGB(255, 51, 153)
ActiveWorkbook.Colors(19) = RGB(102, 102, 255)
ActiveWorkbook.Colors(20) = RGB(102, 204, 255)
End Sub

This macro resets the first four default Fill colors for a chart. All you
need to do is 1. Turn on the macro recorder and then choose Tools, Options,
Colors, and modify any of the chart colors at the bottom of the screen, or in
fact any of the colors at all. Then turn off the recorder.

Cheers,
Shane Devenshire