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.
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.
