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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,872
Messages
5,766,864
Members
425,382
Latest member
IronM

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