fixing pie chart colors depending upon value ?

[Howard]

This is really about using an excel chart inside an access report but nobody
in microsoft.access.reports seems to know the answer.

Using a grouped field in a database I end up with two values per record
showing the number of 'goods' and the number of 'bads' The pie chart plots
these two numbers. I want the 'good' slice always to be green and the 'bad'
slice always to be red.

Setting the format worked OK until I had 7 goods and zero bads, then the
whole chart was red instead of green.

Any idea how I can fix this?

Howard

 

[Jon Peltier]

The technique described in this blog post will work on any chart type that
uses a fill color:

http://peltiertech.com/WordPress/2008/03/03/vba-conditional-formatting-of-charts-by-category-label/

- Jon

 

[Howard]

Thanks, That didn't quite do it but it pointed me in the right direction. In
case anyone else wants to know how to do it this code finally worked.

Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As Integer)

Dim v As Variant

v = Me.chart1.SeriesCollection(1).Points.Item(1).DataLabel.Text
If UCase(Left(v, 1)) = "B" Then 'label starts with B - the bad slice, make
it red
Me.chart1.SeriesCollection(1).Points.Item(1).Interior.Color = RGB(255, 0,
0)
Else 'the good slice, make it green
Me.chart1.SeriesCollection(1).Points.Item(1).Interior.Color = RGB(0, 255,
0)
End If

End Sub