Changing chart colours using VBA

[TommoUK]

Is it possible to change the colours of bars on a chart depending on the
value. For example, if a value is positive, colour the bar green, if
negative, colour it red. The 'invert if negative' check box within Excel sets
the bar to white if negative

 

[JNW]

With my limited knowledge, it seems that each number would need to be a
different series in order to change the color of each individual bar. Unless
your numbers are set up to have all negative numbers in one series and
positive in another.

If that is a possibility than changing the color of the bars is a matter of
recording a macro and cleaning it up.

Hope that at least gets you started.

 

[czywrg]

This code does the trick for me (it is from an earlier posting). Just
select the chart before you run the code.

Sub ColorColumns()

Dim vntValues As Variant
Dim intSeries As Integer
Dim intPoint As Integer

With ActiveChart
For intSeries = 1 To .SeriesCollection.Count
With .SeriesCollection(intSeries)
vntValues = .Values
For intPoint = 1 To .Points.Count
If vntValues(intPoint) < 1 Then
' red column
..Points(intPoint).Interior.Color = _
RGB(255, 0, 0)
ElseIf vntValues(intPoint) > 0 Then
' green column
..Points(intPoint).Interior.Color = _
RGB(0, 255, 0)
End If
Next
End With
Next
End With

End Sub