self adjusting pie chart segment colours based upon set conditions on the work sheet

[tippett]

I am trying work out a way in which to get pie chart segments to sho
one of three colours based upon set percentage conditions on the hos
worksheet for each segment value

 

[Jon Peltier]

Set up a range with your percentages. Say you wanted to identify <20%,
20-40%, and >40%. Put these numbers in the sheet:

0
0.2
0.4

And color the cells they are in with the color you want the wedge (for
red below 20%, color the cell with 0 red). This way is easiest, because
if you want to change the percentage cut offs, or the colors, you can
easily change the sheet, and the macro will still work.

Select your chart, and run this macro:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[tippett]

Jon

Thank you for the reply and coding. I have followed your advice bu
cannot get the macro to accept the 'range' details containing th
required criteria as suggested! What am I missing?

Adrian

 

[Jon Peltier]

Adrian -

Your reply has become disconnected from the thread. What did you try,
and what were the results?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______