Format series of excel pivot chart based on series title

Question

I have a non-stacked pivot bar chart that is showing two values (Sum of Planned Hours & Sum of Actual Hours) for a number of Series.

What I want to achieve is a formatting rule that if all the bars for Planned Hours for each series are shown in solid, and all the bars for Actual Hours are shown in hashed lines.

This chart will be regularly updated with different series e.g. Assy, Insp, Plan but I want the formatting to remain based on the values planned & actual. Potentially I could run a macro after each chart update?

I've included a picture of the desired format with two series included as an example, linked below.

Any/all help would be much appreciated!

Example Chart:

Example Chart

score 1 · Accepted Answer · edited Apr 17 '20 at 07:05

So, I found a solution. The planning series are always evenly numbered series, so I created a loop that runs through the series for all even numbers and applies the correct bar format:

Dim x As Integer

ActiveSheet.ChartObjects("Hours_By_Department").Activate

For x = 2 To ActiveChart.FullSeriesCollection.Count Step 2

    ActiveSheet.ChartObjects("Hours_By_Department").Activate
    ActiveChart.FullSeriesCollection(x).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .Patterned msoPatternNarrowHorizontal
    End With

Next

Format series of excel pivot chart based on series title

1 Answers1