Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Consistent Pie Chart colors (Excel 2000)

    I have a workbook that contains three different 'data' spreadsheets. Each spreadsheet is used to create one to four pie charts. The labels ("Name") for each wedge of the pie will be essentially the same (but definitely in a different order), and some may not appear in a particular chart. I would like to have EACH chart display the same color for a particular "Name". Based upon some searching, it looks like I can add another spreadsheet with all the unique names, and a 'color' for each one. The code that I saw does not work (invalid function, etc.). So, I would like to see what you guys have (and start over).
    ** Also, is it also possible to have any "Name" that is not in the list to be one color or "blank" (no fill)?
    ** I thought that I would use the colors from the standard Excel palette. How do I know the code for each color?

    TIA, Jerry

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consistent Pie Chart colors (Excel 2000)

    I am not sure exactly what you are after.

    You can manually create the colors and ranges after the chart is created.

    You can do it with a macro if desired, though we would need more info if you needed a chart created.

    The color pallete values can be obtained using ColorIndex property. It has values from 1 to 56. The exact color will depend on the user/workbook's color pallete (it is customizable in Tools - options - color)

    You can use the Color property and assign them the exact RGB color you want using the RGB function to give the amount of Red, green, blue desired (each can vary from 0 to 255) so you can give it any number from 0(black) to 16,777,215 (white).

    I do not understand what you are asking here:
    "** Also, is it also possible to have any "Name" that is not in the list to be one color or "blank" (no fill)?"

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Consistent Pie Chart colors (Excel 2000)

    The attached workbook shows the 56 colors available in the Excel palette, with their ColorIndex.

  4. #4
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consistent Pie Chart colors (Excel 2000)

    I have a data file that contains about 10 pieces of 'data' for each of about 150 suppliers. Based upon the output desired, I will create a spreadsheet containing the top 8-10 suppliers for this data item, and group everyone else into "remaining suppliers". So for every chart in the workbook, I would like "Supplier-1" to be "blue", "Supplier-2" to be "green", etc. Since I have 7 to 10 charts, and possibly 15-20 different supplier names, changing each on manually would take a long time. So I would like a macro to do this for me.

    Explaining the second item, "Also, is it possible to have any "Name" that is not in the list to be one color or "blank" (no fill)?". If one of the spreadsheets that generates a chart contains a supplier name that is not in my (color) list, I would like the color for that supplier to default to one color or "no fill".

    Jerry

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consistent Pie Chart colors (Excel 2000)

    <P ID="edit" class=small>(Edited by sdckapr on 26-Apr-05 12:45. added PS)</P>I assume you will create the chart and then run a macro change the colors of the various ranges as desired. To do this:

    For supplier 1 -20 what are the color index desired or the RGB values?

    In the chart, how will the macro know what is "supplier -1"? Is that going to be the name of the "series"?

    In that case the macro can loop thru all all the sheets and in each sheet loop thru the charts and chart objects, lookup each series name in your list and change the color accorgingly. Is this what you are after?

    Steve
    PS what is the default color for "not found"?

  6. #6
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consistent Pie Chart colors (Excel 2000)

    Let me try to explain this with an example.

    Spreadsheet1 will have two columns of data, "Supplier Name", and "Size". This data will be:
    Supplier_Name Size
    supplier-1 88
    supplier-2 82
    supplier-3 75
    supplier-4 67
    supplier-5 54
    remaining 133

    This is used to create pie chart 1.

    Spreadsheet2 will have two columns of data, "Supplier Name", and "Growth". This data will be:
    Supplier_Name Growth
    supplier-1 15
    supplier-2 9
    supplier-5 7
    supplier-33 6
    supplier-42 5
    supplier-11 3
    remaining 13

    This will be used to create pie chart 2.

    I want supplier-1 to ALWAYS be "blue", supplier-5 to ALWAYS be "green", etc.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consistent Pie Chart colors (Excel 2000)

    As I suggested, create the charts and then a macro could be run changing all the colors. on each of the charts.

    You only list 2 colors. "Etc" does not work with colors. What is the colors for 3 - 20 that you want? A colorIndex or an RGB color. There are only 8 named colors...

    Are they going to be named "Supplier-1", etc or will they have real names?

    I imagine a list on a sheet of Names and RGB color. Then when the macro loops thru charts it will lookup the name in the list get the RGB color, and change it on the chart.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consistent Pie Chart colors (Excel 2000)

    Question to Hans: if there are 56 colours and tints possible, how come that the colour chart arising from clicking the 'paint bucket' "Fill" icon only shows 40 of them?
    Thanks
    John Rose, Accounts Clerk
    Handsworth Breakthrough Credit Union Limited

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consistent Pie Chart colors (Excel 2000)

    I am not Hans, but to answer your question:
    The first 40 color indexes are the "fill colors" (paint bucket), the next 8 are the first 8 default chart fills and the next 8 are the first default chart line colors.

    You can change them for a workbook in Tools-options- color tab.

    Steve

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consistent Pie Chart colors (Excel 2000)

    Try the attached.

    It has a macro to create a chart sheet with a pie.

    <pre>Option Explicit
    Sub CreateColorPie()
    Dim cht As Chart
    Dim x As Integer
    Dim iRow As Integer
    Dim rChart As Range
    Dim rColors As Range
    Dim AWF As WorksheetFunction

    Set AWF = Application.WorksheetFunction
    Set rColors = Range("SupplierColors")
    Set rChart = Selection
    Set cht = Charts.Add
    With cht
    .ChartType = xlPie
    .SetSourceData _
    Source:=rChart, _
    PlotBy:=xlColumns
    With .PlotArea
    .Interior.ColorIndex = xlNone
    .Border.LineStyle = xlNone
    End With
    For x = 1 To rChart.Rows.Count
    iRow = 0
    On Error Resume Next
    iRow = AWF.Match(rChart.Cells(x, 1).Value, _
    rColors, 0)
    On Error GoTo 0
    With .SeriesCollection(1).Points(x).Interior
    If iRow = 0 Then
    .Pattern = xlPatternNone
    Else
    .Color = rColors.Cells(iRow).Interior.Color
    .Pattern = xlSolid
    End If
    End With
    Next
    End With
    Set cht = Nothing
    Set AWF = Nothing
    End Sub</pre>


    The range in col A lists the supplier name and the cell color is the color for the pie slices. The range in A is named with dynamic range Insert - name -define:
    Name:
    SupplierColor
    Refersto:
    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

    so it will grow as you add new names. Names not in the list are unfilled. You can add more options to the charts as desired. Just select the chart range and run the macro.

    Steve

  11. #11
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consistent Pie Chart colors (Excel 2000)

    I created a new macro and copied this code into it. Next I highlighted the data for my pie chart, and ran the macro. I received the following error, "Run-time error '1004': Method 'Range' of object '_Global' failed". The error refers to the following step:
    Set rColors = Range("SupplierColors")
    I did not understand if I needed to do something with the info that you had at the end of your note (after the "End Sub" line). Perhaps this is where my problem lies, I don't know. Any thoughts?

    -----------
    By the way, I had to pull together some charts this morning. SInce I couldn't figure out how to fix my error, I created the charts and edited them manually. This is some detail regarding the charts. My final powerpoint slide contained five pie charts. One showed the global summary, and four other pie charts showed the data for each of the four regions of the world. The global chart listed 9 suppliers (by name) and the rest were combined into another supplier named "remaining". Each of the regional charts listed between 4 and 7 suppliers and then "remaining". Thirteen unique suppliers were identified between these five charts.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consistent Pie Chart colors (Excel 2000)

    The range of the supplier names and their colors is named "Supplier colors".

    Do you have this range named as I did (Insert - name - define)?

    Steve

  13. #13
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consistent Pie Chart colors (Excel 2000)

    I create the range as "SupplierColor" (bottom of note), and the code wants "SupplierColors". I corrected this, and executed the macro. The pie chart was created with no error! However, all the pieces of the pie have "no fill". Now, "sheet1" has a lists of the supplier names (column A), and the desired color code (17-26) in column B. What did I miss this time??

    By the way, I assume that it is also easy to:
    1. eliminate the legend
    2. show data labels & percent (xlDataLabelsShowLabelAndPercent)
    3. change label font to 12
    Where should I start looking to find the proper syntax?

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consistent Pie Chart colors (Excel 2000)

    My code does not look for the color in column B (did you modify the code?). My code looked at the color the background cells in column A.

    You could try changing the line:
    .Color = rColors.Cells(iRow).Interior.Color

    to (assuming your "numbers" are color indexes):
    .ColorIndex = rColors.Cells(1).offset(iRow,1).value

    Though if they are "no fill" that means that the value in the "category-column" it is looking up does not match any of the "supplier names" in column A.

    The easiest way to find the objects and syntax, is to record a macro while you make the changes and then add it to the code...

    Steve

  15. #15
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consistent Pie Chart colors (Excel 2000)

    Thank you!! As I assumed, I misunderstood how you were obtaining the color scheme. Once I "filled" the colors in my reference worksheet (sheet1), it worked great.

Posting Permissions

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