CODEWITHSUNDEEP
excelchartsexcel-2010vba
parodytx
parodytx

Reputation: 75

Change Default Colors in Excel 2010 Chart

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.

Upvotes: 0

Views: 927

Answers (2)

parodytx
parodytx

Reputation: 75

Sometimes the hardest solutions to figure out are the most mundane.

the lines

ActiveChart.SeriesCollection(2).Select               
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
With Selection.Format.Fill
...etc

Should be switched to:

ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(2).Select               
With Selection.Format.Fill
...etc

So that the Format.Fill acts on the SeriesCollection property, not the ChartType property. Doh!

Works like a charm now.

Upvotes: 0

bonCodigo
bonCodigo

Reputation: 14361

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.

Upvotes: 0

Related Questions