Like user1813558 in his question "Change the pie chart colors" I would like to choose the colors that Excel uses for a pie chart (indeed for any chart) as I need them to be consistent with other data visualizations we have carefully produced so that a color-blind team mate can still join in data trawl sessions. But rather than write code I would like to do it in the Excel sheet. Is there any way I can put the color data down in the Excel worksheet, for example as a column of RGB hex strings like 7DFF7D, and point the chart at that column for its color choice?
What you would like to do is not possible without code.
The following VBA function can be used to set colors based on cell values for a pie chart in the active worksheet. The color values can be in an N rows x 3 columns range of decimal RGB values or an N rows x 1 column range of 6 digit hex values (where the hex number is a set of three 2-digit hex values in RGB order).
The function requires two arguments: a reference to the range of color values (A1:A5, for example) and the name of the pie chart. The chart name can either be a default name (such as "Chart 2") or a name given to the chart.
If the number of columns in the input range does not equal 3 or 1, or the number of rows does not equal the number of data points in the pie chart, the function returns #N/A! error.
A recolored chart will retain the new colors after the function is deleted from the sheet.
Option Explicit Function SETPIECOLORS(colorRng As Range, chartName As String) As Variant Dim colorArr As Variant Dim myChartObject As ChartObject Dim i As Long Set myChartObject = ActiveSheet.ChartObjects(chartName) ' // Assign RGB decimal color values to array If colorRng.Columns.Count = 3 Then colorArr = colorRng ' // Assign RGB hex color values to array ElseIf colorRng.Columns.Count = 1 Then ReDim colorArr(1 To colorRng.Rows.Count, 1 To 3) For i = 1 To colorRng.Rows.Count colorArr(i, 1) = "&H" & Left(colorRng(i).Value, 2) colorArr(i, 2) = "&H" & Mid(colorRng(i).Value, 3, 2) colorArr(i, 3) = "&H" & Mid(colorRng(i).Value, 5, 2) Next Else ' // Number of columns in color range not equal to 1 or 3 SETPIECOLORS = CVErr(xlErrNA) Exit Function End If With myChartObject With .Chart.SeriesCollection(1) If UBound(colorArr, 1) = .Points.Count Then ' // Set the colors of the pie data points For i = 1 To .Points.Count .Points(i).Interior.Color = RGB(colorArr(i, 1), colorArr(i, 2), colorArr(i, 3)) Next Else ' // Number of rows in color range does not equal number of data points SETPIECOLORS = CVErr(xlErrNA) Exit Function End If End With End With SETPIECOLORS = True End Function
The function can be installed by copying it into a new module inserted via the Developer / Visual Basic menu accessed from the Excel ribbon.