Change column color in graph using Points label-CodePudding

I'm using the Clustered column graph , i'm trying to find a way to change the color of each color the criteria i want to use is if contains.Sadly i'm not able to retrive the point label of the series plotted in the graph. Anyone has a solution to my issue ? Thanks

Edit: I'm trying to achive this via vba code

CodePudding user response：

Assuming that you know how to change the color of a series manually, this is a good place for the Macro Recorder to be used. While recording, the Macro Recorder will "translate" all of the actions you make in Excel into VBA code. https://support.microsoft.com/en-us/office/automate-tasks-with-the-macro-recorder-974ef220-f716-4e01-b015-3ea70e64937b

I made a sample clustered column chart to demonstrate this. It is located on Sheet1. First, I go to the Developer tab in Excel, and under the Code panel, I click the Record Macro button. Then, I manually changed the series color to something else, and I clicked Stop Recording. The following is what the Macro Recorder recorded:

Option Explicit

Sub ChangeCol()
'
' ChangeCol Macro
'

'
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent4
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.25
        .Transparency = 0
        .Solid
    End With
End Sub

There is a lot of extra code here that we don't care about, but that's the nature of the Macro Recorder. It is a great tool to point you in the right direction, but rarely does it give you exactly what you need.

So here is that same code from the Macro Recorder, but without all the extra code:

Option Explicit

Sub ChangeCol()
'
' ChangeCol Macro
'

'
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Fill
        .ForeColor.ObjectThemeColor = msoThemeColorAccent4 ' This changes the color
    End With
End Sub

This code is much cleaner, but we can do better. The .Activate and .Select methods are usually things we can get rid of like so:

Option Explicit

Sub ChangeCol()
'
' ChangeCol Macro
'

'
    ' Reference the chart
    Dim MyChart As ChartObject
    Set MyChart = ThisWorkbook.Sheets("Sheet1").ChartObjects("Chart 1")
    
   ' Change the chart's series 2 color
    With MyChart.Chart.FullSeriesCollection(2).Format.Fill
        .ForeColor.ObjectThemeColor = msoThemeColorAccent4
    End With
End Sub

But what if you want to use your own color instead of one of those theme colors? You can use the .RGB method instead of the .ObjectThemeColor method. And while we're at it, let's make the whole sub more general so that we can use it for any series in a clustered chart:

Option Explicit

Sub ChangeCol(ClusteredChart As Chart, SeriesNumber As Long, Red As Byte, Green As Byte, Blue As Byte)

    Clustered.Chart.FullSeriesCollection(SeriesNumber).Format.Fill.ForeColor.RGB = RGB(Red, Blue, Green)

End Sub

And there you go, a sub to change the color of a series in a clustered chart. This is how that sub would be called:

Private Sub Test()

    Dim MyChart As Chart
    Set MyChart = ThisWorkbook.Sheets("Sheet1").ChartObjects("Chart 1").Chart

    ChangeCol ClusteredChart:=MyChart, SeriesNumber:=2, Red:=0, Blue:=55, Green:=55

End Sub

More importantly than the result of this process, I hope you understand the process itself. Excel's Macro Recorder is a powerful tool for all levels of VBA programmer, and it's a god-send especially when you know how to manually do something, and want to automate it.

Finally, this sub that I've presented is not complete, and that's where you can fix it. For example, there is no check to see if the passed SeriesNumber even exists. And you'll notice that this sub only changes one series' color. If you want to iterate through all series, you'll either have to modify this sub, or do the iteration outside of it and call the sub multiple times.