Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-03-08
6
Medium Priority
?
457 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

580 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