Excel pie chart - fix pie colour by category name

[Frankximus]

Hey guys,

Another question here regarding the pie charts in excel.

I'm building a list of charts with 18 categories in one pie each. The category names are the same however the value/piece of pie size varies. Now when I select the data for each new chart, the color reassigns so there is absolutely no consistency.

Is there a way I can lock the color by name like category name, John/Jane/Brian and then list of values? This way I can easily show whose value has changed. Now all the colours are not consistent (Jane has 18 different colours in 18 different charts since the orders/values have changed).

Thanks!

 

[mole999]

Even more difficult with themes, it can probably be coded in VBA to tidy your outputs, but a pain

 

[pgc01]

Hi

You can loop through all the charts in the worksheet and set the slices colors.

Assuming all charts in the worksheets are your pie charts, try something like:

Sub SlicesColors()
Dim chtO As ChartObject
Dim ser As Series
Dim clf As ColorFormat
Dim j As Long

' loop through all the charts in the worksheet
For Each chtO In Worksheets("Sheet1").ChartObjects
    ' get the reference to the pie chart series
    Set ser = chtO.Chart.SeriesCollection(1)
    
    ' loops through all the points and sets the colors of the slices
    For j = 1 To ser.Points.Count
        Set clf = ser.Points(j).Format.Fill.ForeColor
        
        Select Case ser.XValues(j)
            Case "Jane": clf.RGB = vbYellow
            Case "Mary": clf.RGB = vbGreen
            Case "Kate": clf.RGB = vbRed
        End Select
    
    Next j
Next chtO

End Sub

 

[Frankximus]

Hi

You can loop through all the charts in the worksheet and set the slices colors.

Assuming all charts in the worksheets are your pie charts, try something like:

Sub SlicesColors()
Dim chtO As ChartObject
Dim ser As Series
Dim clf As ColorFormat
Dim j As Long

' loop through all the charts in the worksheet
For Each chtO In Worksheets("Sheet1").ChartObjects
    ' get the reference to the pie chart series
    Set ser = chtO.Chart.SeriesCollection(1)
    
    ' loops through all the points and sets the colors of the slices
    For j = 1 To ser.Points.Count
        Set clf = ser.Points(j).Format.Fill.ForeColor
        
        Select Case ser.XValues(j)
            Case "Jane": clf.RGB = vbYellow
            Case "Mary": clf.RGB = vbGreen
            Case "Kate": clf.RGB = vbRed
        End Select
    
    Next j
Next chtO

End Sub

Hey pgc01,

Thanks so much for your codes. The challenge is that I'm using multiple like 20 + tabs that are based on the same database tab. It's like I have 40+ pivot tables across these 20 tabs that are sourced from the same tab called 'database', which is a direct copy and paste of the latest data source so I only have to update the pivots and not worry about version control.

If I apply this coding, would it affect the other tabs? These series of graphs are only generated in this specific tab, I got other charts that don't need to align color in the rest of the workbook...

I know it's definitely a pain, my last resort is simply to manually adjust the colour one by one...