Format slice of Excel pie chart based on Horizontal category axis

Question

I am looking for a way to format the colour of the slices in a pie chart, based on the values of the horizontal category axis.

For example, I have a pie chart with the following data labels (which are sourced from the category axis labels):

• Apples
• Bananas
• Oranges
• Grapes

The totals of each fruit add up to 100%.

In the pie chart, the slice representing Apples for instance is always coloured GREEN. Bananas is always YELLOW, and so on. Each month I have to update the pie chart, and each month the values of Apples, Bananas, Oranges and Grapes changes. However, the slice colours do not change.

My question is - is there a way to update the pie chart every month and keep the colours associated with each type of fruit? Also keeping in mind, some months may have additional fruits, and some months may omit some fruits.

I have tried changing it according to each slice's Points(x) value, but because the number of series may change from month to month, it wouldn't work. As you can see from the below, I've managed to change it according to each slice's Points(x) value, but am wondering if I could include an IF loop to say something like... IF category axis = "Apples", Selection.Format.Fill ... (fill slice with RGB(X, X, X)) etc.

Sub test()

    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Points(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 144, 44)
        .Transparency = 0
        .Solid
    End With
    ActiveChart.SeriesCollection(1).Points(3).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(112, 48, 30)
        .Transparency = 0
        .Solid
    End With
        ActiveChart.SeriesCollection(1).Points(6).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(112, 48, 160)
        .Transparency = 0
        .Solid
    End With
End Sub

If anyone could assist I'd be very grateful.

Answer 1

Something like this should work

Sub Tester()

    Dim cht As Chart, pts As Points
    Dim sc As Series
    Dim x As Integer
    Dim sliceName As String, clr As Long

    Set cht = ActiveSheet.ChartObjects(1).Chart
    Set sc = cht.SeriesCollection(1)
    Set pts = sc.Points

    'loop through all the points
    For x = 1 To pts.Count

        sliceName = sc.XValues(x)

        Select Case sliceName
            'assign a specific color...
            Case "A": clr = vbYellow 'RGB(255,255,0)
            Case "B": clr = vbGreen  'RGB(0,255,0)
            Case "C": clr = vbRed    'RGB(255,0,0)
            Case Else: clr = -1 '...or do nothing
        End Select

        If clr <> -1 Then
            With pts(x).Format.Fill
                .ForeColor.RGB = clr
                .Transparency = 0
                .Solid
            End With
        End If

    Next x

End Sub