Solved

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

Posted on 2011-03-08
6
436 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
ID: 35071627
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
ID: 35072546
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
ID: 35072586
Rory, I can give you a scenario.... when you want to produce an eyewash ;)

Saqib
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 125 total points
ID: 35081310
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
ID: 35117385
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
ID: 35117389
Perfect! Thanks so much!
Bonnie
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvieā€¦
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilā€¦
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

808 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