PDA

View Full Version : Solved: Conditional Chart (might require VBA?)



TrippyTom
10-18-2006, 02:09 PM
I'm trying to make a bar chart with the colors depending on a separate value (in this case 1 through 5) next to the value of the company. Do I have to resort to VBA to do this?

Andy Pope
10-19-2006, 05:42 AM
You don't have to. Just use 5 data series with the value of each based on the result of formula.
Plot the bar chart as clustered and set the overlap to 100.

See Jon Peltier's page on conditional chart (http://peltiertech.com/Excel/Charts/ConditionalChart1.html)

TrippyTom
10-19-2006, 11:36 AM
Thanks Andy,

I already looked at Jon's pages and couldn't figure how to make it work until today, but it's not as automatic as I would like it to be, so I'm resorting to VBA instead.

Andy Pope
10-19-2006, 11:48 AM
No problem, you didn't say whether you had the code or not.

So something like this....

Sub ConditionalChart()

Dim lngPoint As Long
Dim lngIndex As Long

With ActiveSheet.ChartObjects(1).Chart
With .SeriesCollection(1)
For lngPoint = 1 To .Points.Count
Select Case _
Application.WorksheetFunction.Index(.Values, lngPoint)
Case Is >= 8
lngIndex = 2
Case Is >= 6
lngIndex = 3
Case Is >= 4
lngIndex = 4
Case Is >= 2
lngIndex = 5
Case Else
lngIndex = 6
End Select
.Points(lngPoint).Interior.ColorIndex = lngIndex
Next
End With
End With

End Sub