Programming colors on a pie chart.

[Guest]

Hello there,

I was wondering if someone could help me do a little trick.
I have a pie-chart on one sheet and I want the colors of the pie to match
the color of a cell on a differnt sheet.

That is if cell a1 is green, I want the first slice of the pie to be green.

Is this possible?

 

[Guest]

Hannes,

I kind of do this for a line chart and have provided the VBA that I use
below...

Sub UpdateGraph(p_Sheet As String, p_Graph As String, p_PlotArea As String,
p_PlotSeries As String
'-----------------------------------------------------------------------------------'
' This routine will update line graph plot area dynamically and format lines
and '
' legend to pre-defined settings.

'-----------------------------------------------------------------------------------'

Dim Chart As Chart
Dim i As Integer
Dim Index As Integer

Set Chart = ThisWorkbook.Worksheets(p_Sheet).ChartObjects(p_Graph).Chart
Chart.SetSourceData Source:=Range(p_PlotArea), PlotBy:=xlRows

For i = 1 To Range(p_PlotSeries).Count

Select Case i
Case 1
Index = 3 'Goal - Red
Case 2
Index = 4 'Series 2 - Color
Case 3
Index = 5 'Series 3 - Color
Case 4
Index = 28 'Series 4 - Color
Case 5
Index = 7 'Series 5 - Color
Case 6
Index = 8 'Series 6 - Color
Case 7
Index = 10 'Series 7 - Color
Case 8
Index = 15 'Series 8 - Color
Case 9
Index = 17 'Series 9 - Color
Case 10
Index = 38 'Series 10 - Color
Case 11
Index = 45 'Series 11 - Color
End Select

With Chart.Legend.LegendEntries(i).LegendKey
.Border.ColorIndex = Index
.Border.Weight = xlMedium
.MarkerStyle = xlNone
End With

Next i

Set Chart = Nothing

End Sub

I think you can reference the color index on a cell pretty easily.

HTH, Dean.

 

[Peter T]

I was wondering if someone could help me do a little trick.

I have a pie-chart on one sheet and I want the colors of the pie to match
the color of a cell on a differnt sheet.

That is if cell a1 is green, I want the first slice of the pie to be

green.

Maybe something like this:

Sub test()
Dim cx As Long, n As Long
Dim pnt As Point, ch As Chart, rng As Range
Set rng = Worksheets("Sheet1").Range("A1:A10")
Set ch = ActiveChart
If ch Is Nothing Then
MsgBox "No chart selected"
Else
For Each pnt In ch.SeriesCollection(1).Points
n = n + 1
cx = rng(n).Interior.ColorIndex
If cx < 0 Then cx = xlAutomatic
pnt.Interior.ColorIndex = cx
Next
End If
End Sub

Fill format enough colours in Sheet1!A1:A10 for the number of slices
(points) your pie chart is likely to have.

You will need to run again to update if you change colours in A1:A10.

If you know the name and location of your chart it's not necessary to
select it, eg for a chart on a worksheet:
Set ch = Worksheets("Sheet1").ChartObjects("Chart 1").Chart

Regards,
Peter