Pie Chart - Conditional Formatting with VBA

**clutch12** · 04-24-2009, 02:41 PM

I am trying to create a VBA macro to conditionally format a pie chart in which all slices are the same size but the slice color will vary (and a color can repeat slice to slice) based on an adjacent value being one of three options. In the example in the attached spreadsheet, Column provides the size of the slice and Column C provide the % completion the slice color of which should correspond to the legend in B11:B13. I have used Jon Peltier's VBA code (below) as extracted from this website (http://pubs.logicalexpressions.com/P...cle.asp?ID=390), but I don't know how to adjust the code to get the macro to pick up the % completion values and apply slice colors accordingly.

Please Login or Register  to view this content.

**clutch12** · 04-24-2009, 02:45 PM

I am trying to create a VBA macro to conditionally format a pie chart in which all slices are the same size but the slice color will vary (and a color can repeat slice to slice) based on an adjacent value being one of three options. In the example in the attached spreadsheet, Column provides the size of the slice and Column C provide the % completion the slice color of which should correspond to the legend in B11:B13. I have used Jon Peltier's VBA code (below) as extracted from this website (http://pubs.logicalexpressions.com/P...cle.asp?ID=390), but I don't know how to adjust the code to get the macro to pick up the % completion values and apply slice colors accordingly.

Sub ColorByPercent()
Dim iPtCt As Integer
Dim iPtIx As Integer
Dim iCell As Integer
Dim dTotal As Double
Dim rColor As Range
Dim vVals As Variant

dTotal = 0
Set rColor = ActiveSheet.Range("B11:B13")
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation
Else
With ActiveChart.SeriesCollection(1)
iPtCt = .Points.Count
vVals = .Values
For iPtIx = 1 To iPtCt
dTotal = dTotal + vVals(iPtIx)
Next
For iPtIx = 1 To iPtCt
iCell = WorksheetFunction.Match(vVals(iPtIx) _
/ dTotal, rColor, 1)
.Points(iPtIx).Interior.ColorIndex = _
rColor.Resize(1, 1).Offset(iCell - 1, 0) _
.Interior.ColorIndex
Next
End With
End If
End Sub

**Andy Pope** · 04-25-2009, 07:17 AM

Please remember to use code tags when posting code.

Do you have to use code?

The attached has a slice for each colour for each category.
I used a second pie on the secondary axis to provide the category labels.

Here are some other pie related tricks
http://www.andypope.info/charts/pies.htm

**Andy Pope** · 04-25-2009, 08:41 AM

Also please don't post the same question in multiple sub forums

**somersetdream** · 02-03-2010, 06:14 AM

Originally Posted by Andy Pope

Do you have to use code?

The attached has a slice for each colour for each category.
I used a second pie on the secondary axis to provide the category labels.

Here are some other pie related tricks
http://www.andypope.info/charts/pies.htm

Hi Andy
I have looked at the attached and it looks great, and I have downloaded the 'pies.xls', also great - however, it is not clear how to make the conditional formatting (of the equal pie slices) happen, ie the steps to follow. I too am looking to format a pie of six equal slices with 3 different inputs (rag status for six disciplines), but am not looking forward to learning VBA, so was very pleased to see your post on conditional formatting without the use of code.
I have created my spreadsheet in Office2000, but am hoping to view and edit it later today in Office2007
Any help very gratefully received.
Kind regards,
Sam

**Andy Pope** · 02-03-2010, 06:19 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Hi Sam,

Start you own thread.
Post example of what you have managed to do so far and what exactly is not working.