Change Default Colors in Excel 2010 Chart

Question

I have a Workbook than generates charts on the fly depending on the sheet selected, which is the source of the data. Two of the series are lines, two of the series are bar charts with values above and below one of the line series.

I would like the colors of the bar chart positive values to be green, and the negative values to be red. No matter how many variations I have tried, when the charts are generated, they default to standard colors (burnt orange and aqua blue.)

After the chart is generated, if I edit the series properties directly, I have no problems changing the fill colors to red and green.

My Code:

ActiveSheet.Shapes.AddChart.Select
Application.ActiveChart.Parent.Name = "Chart1"
ActiveSheet.ChartObjects("Chart1").Activate
ActiveChart.PlotVisibleOnly = False

ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = ValueMin - 0.1
ActiveChart.Axes(xlValue).MaximumScale = ValueMax + 0.1    

ActiveChart.SeriesCollection(1).Select                   'Negative Series
ActiveChart.SeriesCollection(1).ChartType = xlColumnClustered
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 0, 0)                      'Red
    .Transparency = 0
    .Solid
End With

ActiveChart.SeriesCollection(2).Select                   'Positive Series
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 176, 80)                     'Green
    .Transparency = 0
    .Solid
End With

ActiveChart.SeriesCollection("3").Select
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 255, 0)                              'Yellow
   .Transparency = 0
End With
ActiveChart.SeriesCollection(4).Select                             'Signal
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 51, 204)                             'Pink
    .Transparency = 0
End With

Any ideas as how to beat the default colors and use the ones chosen? Thanks.

You will have to validate which data series result in a positive value and then apply the colours to the charts based on that. I foresee a loop in the chart generating code. — bonCodigo
Thank you but the data in each series is either all positive or all negative at the outset. The result of charting is to cause a histogram to be created. I want the area above the line to be green, and below it to be red. Data validation will not create the same result. I have updated the code above to show more relevant chart creation if that helps. — parodytx

bonCodigo bonCodigo · Accepted Answer · 2015-07-08T05:23:37

I am a bit lazy to write this code. Therefore providing you with a pseudo code (untested). You may test and use it at your own discretion... Run this code right after the generation of your charts. User will not notice the changes at the backend - if you disable screen updating at the beginning and enable it at the end. I don't see your chart generation code. So I wrote one to do after generation based on your question.

Option Explicit

Public Sub ColureMePlusMinus()
Dim ws as Worksheet  
Dim cht as ChartObject
Dim srs as Series
Dim i as Integer
Dim pcRed as Integer 'if you intend to use RGB, use Long
Dim ncGreen as Integer   

'--change as per your own sheet
Set ws  = ActiveWorkbook.Sheets("Sheet1")

pcRed = 4 
ncGreen = 3 

For Each cht In ws.ChartObjects
   For Each srs In cht.Chart.SeriesCollection
       With srs.Fill
        .Solid
        '--do the honours
       End With
   Next
Next

End Sub

colour credits to peltier.

UPDATE

Updating as per OP's comment:

Changing Excel workbook's colour theme and How to change the default colors that Excel uses for chart series.

Change Default Colors in Excel 2010 Chart

2 Answers