Bar chart: change color of a bar to red if value > x?

  • Thread starter Thread starter =?ISO-8859-15?Q?Christian_M=FCnscher?=
  • Start date Start date
?

=?ISO-8859-15?Q?Christian_M=FCnscher?=

Hi, Group!

Is there a way to change the color of all or a group of bars in a bar
chart, depending on their value? E.g. if the value is below 2.5, the
color shall be green, above 2.5 the color of the whole bar should be red.

Thank you very much!

With kind regards,
Chriss
 
Hi, Jon!

Jon said:
Here's an introduction to conditional charting:
http://peltiertech.com/Excel/Charts/ConditionalChart1.html
Click to expand...

Thank you very much! Thats a great collection of tips, but thats not
what I'm looking for. I don't want do modify the tables. I thougt about
something like:

with all bars do
if cell.value < 2.5 then bar.color = green
else bar.color = red
end with

in a skript assigned to the diagrams.

The values will not be changing very often, so it doesn't need to be
fast. If that's not possible, I think I manually will paint the few bars
red that are above 2.5 ;)

regards,
Chriss
 
Hi,

Something along these lines will colour the bars according to their
values. Under 2 is red, Over 8 is green other values left at default colour.

Sub ColorBars()
Dim intSeries As Integer
Dim intPoint As Integer
Dim vntData As Variant

With ActiveChart
For intSeries = 1 To .SeriesCollection.Count
' set bars to default
.SeriesCollection(intSeries).Interior. _
ColorIndex = xlAutomatic
vntData = .SeriesCollection(intSeries).Values
For intPoint = LBound(vntData) To UBound(vntData)
Select Case vntData(intPoint)
Case Is < 2
' red
.SeriesCollection(intSeries). _
Points(intPoint).Interior.ColorIndex = 3
Case Is > 8
' green
.SeriesCollection(intSeries). _
Points(intPoint).Interior.ColorIndex = 4
End Select
Next
Next
End With

End Sub

Cheers
Andy
 
Back
Top