Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i change the stack of colours chart to values not percent? | Charts and Charting in Excel | |||
how do i change the colours of my pie graph? | Charts and Charting in Excel | |||
Colours of cells change | Excel Discussion (Misc queries) | |||
can do I change autofilter arrow colours | Excel Discussion (Misc queries) | |||
Change the default chart colours | Charts and Charting in Excel |