Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Locking Pie chart wedge colours

    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
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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/...-source-cells/

  3. The Following 2 Users Say Thank You to MartinM For This Useful Post:

    verada (2015-06-15),zeddy (2015-06-15)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Martin

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

    zeddy

  5. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    What were they thinking of when the numbering scheme was chosen ?

    Anyway, print it, spin it - the result isn't white . . .

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    ..done that. Now I look hypnotizzzed

  7. The Following User Says Thank You to zeddy For This Useful Post:

    MartinM (2015-06-15)

  8. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    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
    Attached Files Attached Files

  9. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    On my travels (again) but I'm sure that otherz will help

  10. The Following User Says Thank You to MartinM For This Useful Post:

    verada (2015-06-16)

  11. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    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
    Attached Files Attached Files

  12. #9
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    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

  13. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Verada,

    Try this to see if it meets your needs.

    Maud

    Prior to code run:
    Verada1.png

    After code run:
    Verada2.png

    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
    Attached Files Attached Files

  14. The Following User Says Thank You to Maudibe For This Useful Post:

    verada (2015-06-16)

  15. #11
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Maud - that looks to do exactly what i need.

    Much appreciated

    Regards

  16. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  17. #13
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Rory - I agree. Unfortunately there are those that want the data displayed in a Pie.

    Regards

  18. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I know - they always seem puzzled when I refuse.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •