VBA to change chart colours
 

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

VBA to change chart colours
 

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


--
Regards,
Tom Ogilvy


"Newbie" wrote in message
...
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

VBA to change chart colours
 

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


"Newbie" wrote in message
...
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

VBA to change chart colours
 

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.

--
Regards,
Tom Ogilvy

"Newbie" wrote in message
...
Tom,

This code does the job and I am very grateful, but it fails on the
following
line..

For Each cht In chartsheets

Any ideas why?

Thanks,

Newbs.

"Tom Ogilvy" wrote:

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


--
Regards,
Tom Ogilvy


"Newbie" wrote in message
...
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