Change colour of chart series with VBA

Hi all,

What would be the right line of code (no selection statements if possible) to change the colour of a bar or line series within a chart?

I want to be able to switch between colour schemes, but only change the blue colours and leave the rest untouched!

See image below...

Colour scheme.PNG

Hi
with only a picture its a bit hard to see what's going on in your sheet, but this could get you started.
It changes the line colour of the first series in your chart to red, and the fill colour of the second series (assuming its a bar or similar) to yellow.

Sub ChartCol()
ActiveChart.FullSeriesCollection(1).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
ActiveChart.FullSeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(255, 255, 0)
End Sub

Sorry, maybe this wasn't very clear. I have attached a sample workbook to work with. I basically want to select a colour from the Form Control ComboBox and then have it take the colour code from the control sheet and apply it to all charts where bars or lines are blue, while leaving the white bars or lines white...

P.S. Ignore the other metrics form control combobox

As I had mentioned, just ignore the other form control combobox, it only moves the series range down or up and isn't relevant for this. I just want to change all blue bars, lines into orange when I select orange in my colour combobox and back to blue whenever I select blue.

I just tried your line of code and get below error message. Also, I wasn't exactly sure what range I need to define as "ColRange" and which as "Outrange"? Shall "ColRange" refer to cells F10:I10 where my 4 different colour are? and then "Outrange" refers to the form control cell link with the selected colour?

forum.PNG

This was the line of code with the error:

For Each ss In ActiveChart.SeriesCollection

Hi
Yes, ColRange should be a range of cells each filled with a colour corresponding to the colours in your combobox, and outrange if the form control cell. The macro should change the colour of any series in the active chart whose line colour is the same as the first colour in your drop down list to the colour you select. So it needs 3 things - a named range ColRange, a named cell Outrange, and for you to have a chart activated.

I got the same error when I had the drop down box selected rather than the chart - perhaps that is the issue.

You are right, if I select a chart before, the error does not appear, though, it also doesn't change any bar or line colour...

Hi

the colour it searches for is the colour of the first cell in ColRange. If no series has that colour, none will change.

It may be than none of your series lines is exactly that colour.

Colours in excel are very precise - there are more than 16 million possible colours, and thousands of these could be considered "blue". you don't appear to be using the standard excel blue shade (xlBlue, or RGB(0,0,255)), which is why I tied the macro to a cell in your colour range, not a particular colour value.

It may be possible to specify colours that are blue-ish (ie more blue than red or green on the RGB spectrum) but that will pick up a lot of colours that don't look blue.

It may be possible to specify colours that are blue-ish (ie more blue than red or green on the RGB spectrum) but that will pick up a lot of colours that don't look blue.

Hence, I guess it might be possible to just change ALL data series that are NOT white-ish to the colour I select in my combobox?

Hi humdingaling

Glad to see you again on one of my threads

white-ish is not a technical term
you need to set all your charts to be exactly the color you have setup in control tab

secondary issue is the syntax for changing color in charts is different when it is a line chart compared to when it is a bar chart
so you also need to cater for that too

alright, this seems to be getting to complicated for such a minor thing. Can I just use a Macro that calls each of the chart lines and bars separately and changes its colour to what is selected in my Form Control ComboBox? Basically a more specific approach with defining chart names and lines rather than a general "change certain lines that have a certain colour" approach...

Something like the below...

Sub ChangeColourCharts()
            
    With Worksheets("Store Tracker")
        .ChartObjects("StorePerformanceChart").Chart.SeriesCollection(1).Interior.Color.RGB = Outrange
    End With
            
End Sub

Impressive how fast you solve these issue humdingaling! Thanks!!

I have just tried and it works incredible well in the sample workbook, however, my actual workbook is using slightly different white-ish colours I just realised and it doesn't work for some charts anymore. Hence, since I believe this is a brilliant method, but at the same time a little too flexible (in case colours change in the future), I would probably prefer an approach such as below, where each chart and its data series is called and then its colour changed based on what is selected in my combobox. Can you help me adjust below code to do just exactly that for all charts? I assume I would just have to change the chart name and sometimes "line" to "points" as you had mentioned with doughnut charts?

Sub RecolourCharts_Change()

    Dim MyCol As Long
    Dim shp As Shape
    Dim subShp As Shape
    Dim ss As Series
            
    MyCol = Worksheets("Control").Range("F16").Offset(, Worksheets("Control").Range("E13").Value).Interior.Color
        
    subShp.Name("StorePerformanceChart").Chart.SeriesCollection(1).Format.Line.ForeColor.RGB = MyCol
    subShp.Name("Chart 7").Chart.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = MyCol

End Sub

Also, what is the syntax for "glow"? how would I need to refer to that in my code?

actually white is a bit harder than blue because its not a single colour but a large concentration of other colours. This variation will change the colour of all pale lines (with red, green and blue values in the RGB formula all > 200) to the selected color in the combobox

Sub RepPale()
Dim ss, MyCol, Bcol, rr, gg, bb, LineCol
MyCol = Range("ColRange").Cells(Range("Outrange").Value).Interior.Color
Bcol = Range("ColRange").Cells(1).Interior.Color

For Each ss In ActiveChart.SeriesCollection
    LineCol = ss.Format.Line.ForeColor.RGB
    bb = WorksheetFunction.RoundDown(LineCol / (256 ^ 2), 0)
    gg = WorksheetFunction.RoundDown((LineCol - (256 ^ 2) * bb) / 256, 0)
    rr = LineCol - (bb * (256 ^ 2)) - (256 * gg)
    
    If rr > 200 And gg > 200 And bb > 200 Then ss.Format.Line.ForeColor.RGB = MyCol
Next ss
End Sub

Thank You!!! Just what I was looking for! I think this a lot simpler to implement than the other method.

I just somehow don't seem to get the code right with the glow transparency! Can you help me fix below code?

Sub ReColourChart_Change()

    Dim MyCol As Long
            
    MyCol = Worksheets("Control").Range("F16").Offset(, Worksheets("Control").Range("Selected_Colour").Value).Interior.Color

    With ActiveSheet.Shapes("Evolution Chart").Chart.SeriesCollection(1).Format.Line
        .ForeColor.RGB = MyCol
        .Glow.Color.RGB = MyCol
        .Glow.Transparency = 85
    End With
        
    With ActiveSheet.Shapes("Bullet Graph").Chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = MyCol
    End With
             
    With ActiveSheet.Shapes("Contribution Chart").Chart.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = MyCol
    End With
        
    With ActiveSheet.Shapes("AUR Chart").Chart.SeriesCollection(1).Format.Fill
        .ForeColor.RGB = MyCol
        .Glow.Color.RGB = MyCol
        .Glow.Transparency = 40
    End With
        
    With ActiveSheet.Shapes.Range(Array("Location Icon", "Store Type Icon")).Fill.ForeColor.RGB = MyCol
    End With
    
End Sub

you've got it under the wrong "with"

peg back the with statement to a "lower level"

try these instead

That actually wasn't it! I tried back and fourth, still didn't work! Figured it was the transparency value itself as it should be between 0 and 1 as it is a percentage. working perfectly fine now

when in doubt...macro recorder it

I tried! Somehow it doesn't capture chart formatting for me...

One last thing (promise!), If I have a heat map with a colour range of 5 or 10 shades. How can I get the Macro to change that? I have all colour shades already defined on my control sheet...

heat map.pmg.PNG

i dont have access to heat maps in my version of excel so cannot test the syntax out
i did a quick google and it looks like i found the something that looks like the base code

Damn it! No worries, will try it out. Thanks for doing a google search and all your other help so far!