VBA to change chart colours

[Guest]

Hi.

I'm after some VBA to loop through all my bar charts and change the fill
colour to blue for the positive number and fill colour to red for the
negative numbers.

Grateful for any help!

TIA

 

[Tom Ogilvy]

I didn't put any code in to check the type of chart - I assumed they were
all the same type (column Chart most likely - bar charts are horizontal).

run the code on a copy of your workbook in case there are unwelcome side
affects. I know for a chart with a single series, it changed the legend for
me. Excel made this adjustment, not the code.

Sub TesterAAA()
Dim cObj As ChartObject
Dim cht As Chart, pt As Point
Dim varr As Variant
For Each sh In Worksheets
For Each cObj In sh.ChartObjects
Set cht = cObj.Chart
For Each ser In cht.SeriesCollection
varr = ser.Values
i = 0
For Each pt In ser.Points
i = i + 1
If varr(i) >= 0 Then
pt.Interior.ColorIndex = 5
Else
pt.Interior.ColorIndex = 3
End If
Next
Next
Next
Next sh
For Each cht In chartsheets
For Each ser In cht.SeriesCollection
varr = ser.Values
i = 0
For Each pt In ser.Points
i = i + 1
If varr(i) >= 0 Then
pt.Interior.ColorIndex = 5
Else
pt.Interior.ColorIndex = 3
End If
Next
Next
Next

End Sub

 

[Bernie Deitrick]

Newbie,

The easiest (and best) way to do that is to split your bar chart data series
into two columns: negative values in one, and positive in the other, then
used a stacked bar chart.

So, instead of 2 columns

Month Value
Jan -2
Feb 3

use 3 columns

Month NegVal PosVal
Jan -2
Feb 3

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

Yes, mental glitch.

It should be:

for each cht in Charts

You would only need this if you had Chart Sheets rather than charts embedded
on worksheets.