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

[=?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

 

[Jon Peltier]

Here's an introduction to conditional charting:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

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

 

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

Hi, Jon!

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

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

 

[Andy Pope]

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

 

[Ed Ferrero]

Hi Christian,

There is a sample file at http://edferrero.m6.net/charting.aspx
Look for Conditional Chart Formatting.

Works much like Andy Pope's code.

Ed Ferrero
http://edferrero.m6.net/