Bar charts colour

[hughess7]

Hi all

Does anyone know how to alter the colour of a column on a bar chart,
depending on its value? I want to change the columns that have a value over
100 to red and the columns that have a value less than 100 a different
colour. There are two charts on the one report and I need to do the same with
both charts (based on different datasources). Both are based on a query which
produces two records, one has dealer data and the 2nd row is the national
data which is always 100 and is shown on the chart as a trend line. I just
want the columns for the dealer record to be affected by the colour change.

Thanks in advance for any help.
Sue

 

[RonaldoOneNil]

The following is for a chart called chtTest on a form and the code is in the
forms On Load event. My example did not have the datalabel showing on each
bar so I had to turn it on to get the value and then turn it off agian. If
you show the value on your chart then you do not need to turn it on and off.

Dim i As Integer
Dim chtTemp As Graph.Chart

Set chtTemp = Me.chtTest.Object

For i = 1 To chtTemp.SeriesCollection(1).Points.Count
chtTemp.SeriesCollection(1).Points(i).HasDataLabel = True
If Val(chtTemp.SeriesCollection(1).Points(i).DataLabel.Text) > 100
Then
chtTemp.SeriesCollection(1).Points(i).Interior.ColorIndex = 3
Else
chtTemp.SeriesCollection(1).Points(i).Interior.ColorIndex = 5
End If
chtTemp.SeriesCollection(1).Points(i).HasDataLabel = False
Next i

 

[hughess7]

Thanks, my chart is on a report but I tried this and I get an error 1004 -
unable to get the text property of the datalabel class...

I had to change Dim chtTemp As Graph.Chart to Dim chtTemp as Object to get
this to compile - I suspect it is using a different reference or something ?

 

[hughess7]

oops sorry! Just tried with turning the datalabels on and off and yes it
works for me too!!! Thank you so much, I've been trying to crack this for a
while now.

Brilliant thanks

Sue