Locking Pie chart wedge colours

Topic

New Reply

Hi All,

Does any one know if there is away to format pie wedges based on predetermined colours so that when comparing 2 charts with similar data (in this case names) that the colour of the wedge for a name is the same in both charts, regardless of whether all names appear in both charts?

I hope that makes sense – the attached may help explain further.

Thanks in advance for any thoughts/assistance

Regards

Reply | Quote

Replies

Just last week I had bookmarked a webpage in my “Interesting ideas for Excel” folder.

It seems to be able to achieve what you want, rather neatly. I have NOT tested it, but pass it on for what it’s worth.

http://datapigtechnologies.com/blog/index.php/color-pie-chart-slices-to-match-their-source-cells/

Reply | Quote

Hi All,

Thanks for the assistance.

You are correct MartinM, this is very close to what I’m after.

I’d like to change the code so that it refers to the colour in column T rather than AA or AH, I’ve attached a working file so you can see what I mean.

Or is there away to do this, maybe conditional formatting to match the colours to the names in AA and AH.

Any thoughts would be much appreciated

Reply | Quote

Hi Verada

see attached file with amended code.
The colours from column [T] are used as requested.
..but only the names in column [AH] are in the same order as those in column [T]
You will need to ‘line up’ the names in column [AA] to match those in column [T] if you want to use the same colours.

zeddy

Reply | Quote

Hi Martin

..thanks for that link.
..that 56-colour pie image is now burned into my brain.

zeddy

Reply | Quote

What were they thinking of when the numbering scheme was chosen ?

Anyway, print it, spin it – the result isn’t white . . .

Reply | Quote

..done that. Now I look hypnotizzzed

Reply | Quote

On my travels (again) but I’m sure that otherz will help :rolleyes:

Reply | Quote

Hi Zeddy,

Thanks for your help with this. That works Ok, but sort of defeats the purpose to have the pie wedges the same colour in both charts for the individual.

Rather than getting the colours from [T] is it possible to match the name colours in [AA] and [AH] with those in [T]? I guess a bit like a conditional format where if the name, say = Lara in [AA] or [AH] and the corresponding cell colour (orange in T5) that way the names would always have the same wedge colour (based on the previous code).

Hope that makes sense

Regards

Reply | Quote

Verada,

Try this to see if it meets your needs.

Maud

Prior to code run:
41050-Verada1

After code run:
41051-Verada2

Code:

Public Sub PieColors()
Dim Cht1 As Object, Cht2 As Object
Dim I As Integer, J As Integer
Set Cht1 = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
Set Cht2 = ActiveSheet.ChartObjects(2).Chart.SeriesCollection(1)
    For I = 1 To Cht1.Points.Count
        FillPoint 1, I
    Next I
    For J = 1 To Cht2.Points.Count
        FillPoint 2, J
    Next J
End Sub

Public Sub FillPoint(ByVal ChtNum As Integer, ByVal num As Integer)
    ActiveSheet.ChartObjects(ChtNum).Activate
    Label = Split(ActiveChart.SeriesCollection(1).Points(num).DataLabel.Text, “,”)
    Row = WorksheetFunction.Match(Label(0), [T2:T14], 0)
    ColorValue = Cells(Row + 1, 20).Interior.Color
    ActiveChart.SeriesCollection(1).Points(num).Interior.Color = ColorValue
End Sub

Reply | Quote

Thanks Maud – that looks to do exactly what i need.

Much appreciated

Regards

Reply | Quote

Pie charts are generally inappropriate choices. Trying to compare 2 pie charts is just evil. The mere fact that you have to label all the points for the chart to really tell you anything is a good indicator – you might as well just use a table.

Reply | Quote

Hi Rory – I agree. Unfortunately there are those that want the data displayed in a Pie.:confused:

Regards

Reply | Quote

I know – they always seem puzzled when I refuse.

Reply | Quote