Use VBA to set color in pie charts

[Tcurtis]

Is there a way to set the colors in a pie chart without having to manipulate them everytime I run a report graph? I have 6 different categories that are counted and I want each category to not vary from month to month in color.
Lost = Red
Found = Green
Obsolete = Yellow, etc.
Can I assign colors to a table or can I use VBA in the chart to acheive this?

Thanks

 

[pbornemeier]

There is probably a better way, but courtesy of the brute force method:

Option Explicit

Sub SetPieChartColorsBySliceName()

    Dim lX As Long
    Dim arySeriesData, aryPointsNameLocation, aryPointNames
    Dim namesWorksheet As String
    Dim namesRange As String
    
    With ActiveSheet.ChartObjects(1).Chart
        
        If .Type <> 5 Then
            MsgBox "The first chart on this worksheet is not a Pie Chart."
            GoTo End_Sub
        End If
        
        arySeriesData = Split(ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).formula, ",")
        aryPointsNameLocation = Split(arySeriesData(1), "!")
        namesWorksheet = aryPointsNameLocation(0)
        namesRange = aryPointsNameLocation(1)
        aryPointNames = Worksheets(namesWorksheet).Range(namesRange)
        
        For lX = LBound(aryPointNames, 1) To UBound(aryPointNames, 1)
            .SeriesCollection(1).Points(lX).Select
            
            Select Case aryPointNames(lX, 1)
            Case "Lost"
                Selection.Format.Fill.ForeColor.RGB = RGB(255, 100, 0)
            Case "Found"
                Selection.Format.Fill.ForeColor.RGB = RGB(100, 255, 0)
            Case "Obsolete"
                Selection.Format.Fill.ForeColor.RGB = RGB(100, 0, 255)
            Case Else
                Selection.Format.Fill.ForeColor.RGB = RGB(0, 0, 0)
            End Select
            
        Next

    End With

End_Sub:

End Sub