Solved: Conditional Chart (might require VBA?)

[TrippyTom]

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]

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

[TrippyTom]

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]

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