Solved

Excel Pie Chart - can I change the starting slice color in the automatic color index?

Posted on 2011-03-08
6
429 Views
Last Modified: 2012-05-11
I am using VBA to automate the creation of a bunch of worksheets with pie charts in Excel from MS Access. There will be two pie charts on each worksheet, and a worksheet for each Department (around 30 total).

I have figured out that the default color scheme is that first slice of each pie chart starts with ColorIndex #17, and proceeds from there one by one (18, 19, 20, etc)

What I hope to do is to have the Color Index start from a different number for the second chart, for example start with color index #7. I have searched online and have not seen any references to this at all. Is it possible?

The reason for this is that with the two pies side by side, people naturally think that the first color of each pie correspond to each other, but they do not.

I tried assigning colors to the specific labels for a slice (using Select Case), but this yielded some extremely ugly color combinations in some of the pies. Yuck.

Anyone have any idea if this can be done? Even more important, can someone tell me how to do it!?

Thanks,
Bonnie
0
Comment
Question by:BonnieH
  • 3
  • 2
6 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Most things can be done with VBA. :)
My first question would in all honesty be:  do you really want pie charts at all? There is almost never a scenario where a different chart type isn't clearer than a pie chart.
0
 

Author Comment

by:BonnieH
Comment Utility
Hello Rorya, I get your point about pie charts, but it's my boss's boss that has specified the pie charts, after conferring with his boss, and so Pie Charts it is!

Bonnie
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Rory, I can give you a scenario.... when you want to produce an eyewash ;)

Saqib
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 125 total points
Comment Utility
Ah, management. Understood. :)

Something like:
Sub testingfmtcht()
   FormatPieChart 7, ActiveSheet.ChartObjects(2).Chart
End Sub
Sub FormatPieChart(lngStartIndex As Long, cht As Chart)
' formats slices of a pie chart, starting at the provided colorindex
   Dim n As Long
   With cht.SeriesCollection(1)
      For n = 1 To .Points.Count
         With .Points(n).Interior
            .ColorIndex = lngStartIndex + n - 1
            .Pattern = xlSolid
         End With
      Next n
   End With
End Sub

Open in new window

0
 

Author Comment

by:BonnieH
Comment Utility
Eureka! This worked. Thanks Rorya!
Sorry it took me a couple of days to respond. I did not get an email notification, and had assumed that this was beyond hope.

This is just what I wanted. Thanks so much.
Bonnie
0
 

Author Closing Comment

by:BonnieH
Comment Utility
Perfect! Thanks so much!
Bonnie
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now