How to make the color change stick in a PivotChart-II

steveski

Board Regular
Joined
Feb 9, 2004
Messages
55
I have a PivotChart (bar chart). When I format a data series and change the color of the bar, it changes color OK. However, when I refresh the PivotChart, the color reverts to the default. I understand this is a known problem.

I tried recording a macro to capture the code for changing the bar colors. Then I put the macro code in the Chart’s VBA code to run when the chart automatically refreshes. This works fine, as long as all the series are present. However, it is possible sometimes for one or more series to be absent from the list. When this happens, and I run the code, I get an error because VBA is trying to change the color of a series collection that is absent. The other problem with this method is that it cannot differentiate between the series, each of which should have its own color. If the ‘first’ series is missing, then the ‘second’ series gets the ‘first’ series’ color.

My data series are designated ‘E’, ‘R1’, ‘R2’, ‘R3’, ‘PM’, and ‘O’. I’m thinking I could assign a color index to each series in my data entry sheet. Then the PivotTable could get the color indices. Then (hopefully), the PivotChart could get and use the respective color indices, regardless of which series are present or absent.

Does this sound feasible? If yes, can you help me figure out how to get the PivotChart to get and use the color indices?

Thank you.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,186,362
Messages
5,957,410
Members
438,304
Latest member
duck90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top