Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
Perhaps the approach outlined in this link would help? –  chuff Apr 19 '13 at 15:52
    
I don't think that is specifying the colors used. Can you be more specific about where in the approach he applies colors specified in the data? –  dumbledad Apr 19 '13 at 16:09
    
Without using VBA I don't think it would be possible. There's no option in the UI to specify the color based on Cell values. –  Brad Patton Apr 19 '13 at 16:57
    
Thanks Brad. If you add this as an answer I'll mark it as such. –  dumbledad Apr 22 '13 at 7:23

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Interesting code, thanks. There's also code in the answer to the question I reference in mine. But the question I ask is how to do it without code so this is not an answer. –  dumbledad Apr 22 '13 at 7:23
    
Cannot be done without code. –  chuff Apr 22 '13 at 12:13
    
Thanks Chuff; would you add that comment to the start of your answer and then I'll mark it as the answer. –  dumbledad Apr 25 '13 at 5:52
    
Having added the function to my workbook how do I actually run it? That's got to be obvious but I only seem to be offered macros to run. –  dumbledad Apr 25 '13 at 6:19
    
Got it - I needed to add your code to modules not objects in the VBA window. Thanks again –  dumbledad Apr 25 '13 at 9:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.