Retain colours in Pie Chart with Dynamic Range

[Drofidnas]

Hi

I have a dynamic range which populates a pie chart with up to 5 values:

Not Started - blue
Complete - green
At Validation - orange
Overdue - red
In Progress - yellow

If any of the values are zero, the range contracts so I don't get a null slice. Basic stuff.

What is happening though is that the assigned colours for the slices are not staying with their respective definitions, rather they are fixed to the SeriesCollection.Points, regardless of what is missing and where.

ie if Not started and Complete are zero then At Validation will be the first SeriesCollection(1).Point(1) and be blue, Overdue (2) will be green and In Progress (3) orange.

As a workaround, I've tried to programmatically change the colours but I can't find how to say for example:

If SeriesCollection(1).Points(1).Value = "Not Started" Then 
  ActiveChart.SeriesCollection(1).Points(1).Select
    With Selection.Format.Fill
      .ForeColor.RGB = RGB(255, 255, 0)
    End With
End If

(Clearly this is invalid!)

So
a. is there a way to fix the colours with the SeriesCollections or failing that
b. what is the correct syntax for identifying a SeriesCollection name?

Thanks in advance
Chris

 

[AlphaFrog]

Maybe try something like this (not tested).

[color=darkblue]Dim[/color] srs [color=darkblue]As[/color] Series
[color=darkblue]For[/color] [color=darkblue]Each[/color] srs [color=darkblue]In[/color] ActiveChart.SeriesCollection
    [color=darkblue]Select[/color] [color=darkblue]Case[/color] srs.Name
        [color=darkblue]Case[/color] "Not Started": srs.Fill.ForeColor = vbBlue
        [color=darkblue]Case[/color] "Complete": srs.Fill.ForeColor = vbGreen
        [color=darkblue]Case[/color] "At Validation": srs.Fill.ForeColor = RGB(255, 102, 0) [color=green]'Orange[/color]
        [color=darkblue]Case[/color] "Overdue": srs.Fill.ForeColor = vbYellow
        [color=darkblue]Case[/color] "In Progress": srs.Fill.ForeColor = vbRed
    [color=darkblue]End[/color] [color=darkblue]Select[/color]
[color=darkblue]Next[/color] srs

 

[Drofidnas]

Thanks AlphaFrog, that's not quite working though (Fill.Forecolor gives an error) but it might be a step in the right direction.
I changed them to Fill.Forecolor.ObjectThemecolor which is legal but does nothing.

Stepping through, it's ignoring the Cases so I don't think the Name value is correct.

 

[AlphaFrog]

Thanks AlphaFrog, that's not quite working though (Fill.Forecolor gives an error) but it might be a step in the right direction.
I changed them to Fill.Forecolor.ObjectThemecolor which is legal but does nothing.

Stepping through, it's ignoring the Cases so I don't think the Name value is correct.

When you step through, hover your mouse over srs.Name to see its' current value.

I was assuming each series had a defined name. If not, how do you identify a series?
If the 1st data point is an ID, then try something like this...
Select Case srs.Points(1).Value

 

[Drofidnas]

I guess this is my ignorance over the construction of the chart and its componenet elements. Apologies
My code is:

Range("B15").Value = "SB" Then
ActiveSheet.ChartObjects("Pie_View").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Name = "=Overview!$A$30"
    ActiveChart.SeriesCollection(1).Values = "=PieDataSB"
    ActiveChart.SeriesCollection(1).XValues = "=PieLabelSB"
End If

So the user selects SB from a data validation list in B15 which triggers the appropriate data to be displayed in the pie chart via the dynamic range PieDataSB.
It's the XValues labels that need the colours attaching to them. I can't adapt your code to suit.

 

[AlphaFrog]

It's the XValues labels that need the colours attaching to them.

I still do not understand.

Perhaps if you make a chart. Then start recording a macro and manually change the colors of whatever you want to change. Stop recording and post the code here. That recorded code will show me what you want changed.

 

[Drofidnas]

OK, this is selecting a slice and changing the colour.
However your code only refers to the name of the Series, It's the Horizontal (Category) Axis labels that refer to the dynamic range that I need to change

Thanks for your time - I'm a bit **** with charts

Sub Macro11()
'
' Macro11 Macro
'

'
    ActiveSheet.ChartObjects("Pie_View").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Points(3).Select
    ActiveChart.ChartGroups(1).FirstSliceAngle = 46
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
End Sub

 

[AlphaFrog]

This is how to loop through each data point in SeriesCollection(1) if that's what you want.

    [color=darkblue]Dim[/color] pt [color=darkblue]As[/color] Point
    ActiveSheet.ChartObjects("Pie_View").Activate
    [color=darkblue]For[/color] [color=darkblue]Each[/color] pt [color=darkblue]In[/color] ActiveChart.SeriesCollection(1).Points
        [color=darkblue]Select[/color] [color=darkblue]Case[/color] pt.Value
            [color=darkblue]Case[/color] "Not Started": pt.Format.Fill.ForeColor.RGB = vbBlue
            [color=darkblue]Case[/color] "Complete": pt.Format.Fill.ForeColor.RGB = vbGreen
            [color=darkblue]Case[/color] "At Validation": pt.Format.Fill.ForeColor.RGB = RGB(255, 102, 0) [color=green]'Orange[/color]
            [color=darkblue]Case[/color] "Overdue": pt.Format.Fill.ForeColor.RGB = vbRed
            [color=darkblue]Case[/color] "In Progress": pt.Format.Fill.ForeColor.RGB = vbYellow
        [color=darkblue]End[/color] [color=darkblue]Select[/color]
    [color=darkblue]Next[/color] pt

 

[Drofidnas]

Hi AlphaFrog

That code errors out around the pt.Value.
However I've solved the problem myself by interrogating the cells in the dynamic range that feed the chart and taking the colour logic from there. It's a bit clumsy but it works. It would be nice to use your code though to keep it clean.

But thanks for your help, appreciate your time.