VBA coloring columns in chart

[Dimitris254]

i have several clustered column charts that i'm struggling to color them in a specific way

so, the default Excel colors look something like that: http://i.imgur.com/rBL3vBi.jpg
what i want is this: http://i.imgur.com/rAZDzGv.jpg
the table is this (ploted by columns in the macro): http://i.imgur.com/SjqlEJO.jpg

right now, in order to go from default to desired format, i have to click every single data point > fill color, which, needless to say, is a pain..

any help would be highly appreciated

 

[Worf]

Hi

Sub Clustered()
Dim co As ChartObject, xv, j%, i%, p As Point
Set co = ActiveSheet.ChartObjects("Chart 12")
For i = 1 To co.Chart.SeriesCollection.Count
     xv = co.Chart.SeriesCollection(i).XValues
     For j = 1 To UBound(xv)
        Set p = co.Chart.SeriesCollection(i).Points(j)
        Select Case i Mod 2
            Case 0                                                                              ' dark
                If xv(j) = "s/w cost" Then p.Format.Fill.ForeColor.RGB = RGB(40, 120, 170)      ' blue
                If xv(j) = "h/w cost" Then p.Format.Fill.ForeColor.RGB = RGB(20, 130, 20)       ' green
                If xv(j) = "fte" Then p.Format.Fill.ForeColor.RGB = RGB(200, 80, 20)            ' orange
            Case 1                                                                              ' light
                If xv(j) = "s/w cost" Then p.Format.Fill.ForeColor.RGB = RGB(170, 210, 240)     ' blue
                If xv(j) = "h/w cost" Then p.Format.Fill.ForeColor.RGB = RGB(190, 250, 190)     ' green
                If xv(j) = "fte" Then p.Format.Fill.ForeColor.RGB = RGB(250, 200, 180)          ' orange
        End Select
    Next
Next
End Sub

 

[Dimitris254]

thanks Worf, i'll try it tomorrow and report back with the results!

a couple questions about the logic;
- the Select Case i Mod 2 is used to define the data points or the series?
- xv = values of serie's data points (or number of data points? ) and is it defined as variant?

Also, since i need the data table under the charts, is there a way to get rid of the colored squares next to the "2016 Q1" etc?

 

[Worf]

- The select case is based on the series index. If it is an even number, a dark colour is applied; an odd number results in a light colour. My example chart has seven series.
- Xv is an array containing the three possible values for the horizontal axis. When nothing is said, a variable defaults to variant.
- If you mean the legend, add this:

co.Chart.HasLegend = False

 

[Dimitris254]

just tried the code Worf, it works almost perfect ! Imgur: The most awesome images on the Internet

remaining issues:
- legend doesn't go away (least importance)
- as you notice, the last column is Q2, meaning it has to be dark colored. It's the only "inconsistency" in the table data (ie. last year has only Q2, no Q1)

i'm trying to figure out where & how the exclusion must be set, but no luck so far. Note that i've tweaked the code to read color values from nearby cells and that light colors are the same as the dark ones, with 75% transparency:

Option Explicit

Sub Clustered()


Dim co As ChartObject
Dim xv As Variant
Dim j As Integer
Dim i As Integer
Dim p As Point


Set co = ActiveSheet.ChartObjects(1)


For i = 1 To co.Chart.SeriesCollection.Count
     xv = co.Chart.SeriesCollection(i).XValues
     
     For j = 1 To UBound(xv)
        Set p = co.Chart.SeriesCollection(i).Points(j)
        
        Select Case i Mod 2
            Case 0
                If xv(j) = "S/W cost" Then p.Format.Fill.ForeColor.RGB = ActiveSheet.Range("A22").Interior.Color
                If xv(j) = "H/W cost" Then p.Format.Fill.ForeColor.RGB = ActiveSheet.Range("A23").Interior.Color
                If xv(j) = "FTE" Then p.Format.Fill.ForeColor.RGB = ActiveSheet.Range("A24").Interior.Color
            
            Case 1
                If xv(j) = "S/W cost" Then p.Format.Fill.ForeColor.RGB = ActiveSheet.Range("A22").Interior.Color
                If xv(j) = "H/W cost" Then p.Format.Fill.ForeColor.RGB = ActiveSheet.Range("A23").Interior.Color
                If xv(j) = "FTE" Then p.Format.Fill.ForeColor.RGB = ActiveSheet.Range("A24").Interior.Color
                
                If j <> 11 Then p.Format.Fill.Transparency = 0.75
                
        End Select
    Next
Next


co.Chart.HasLegend = False


End Sub

 

[Worf]

1) New version below.
2) I will look into the coloured squares issue later.

Sub Clustered()
Dim co As ChartObject, xv As Variant, j As Integer, i%, p As Point
Set co = ActiveSheet.ChartObjects("Chart 12")
For i = 1 To co.Chart.SeriesCollection.Count
    xv = co.Chart.SeriesCollection(i).XValues
    For j = 1 To UBound(xv)
        Set p = co.Chart.SeriesCollection(i).Points(j)
        If xv(j) = "S/W cost" Then p.Format.Fill.ForeColor.RGB = ActiveSheet.[A22].Interior.Color
        If xv(j) = "H/W cost" Then p.Format.Fill.ForeColor.RGB = ActiveSheet.[A23].Interior.Color
        If xv(j) = "FTE" Then p.Format.Fill.ForeColor.RGB = ActiveSheet.[A24].Interior.Color
        Select Case i Mod 2
            Case 1
                If i <> co.Chart.SeriesCollection.Count Then p.Format.Fill.Transparency = 0.75
        End Select
Next j, i
co.Chart.HasLegend = False
End Sub

 

[Dimitris254]

thanks again Worf

i've been experimenting with the chart, in particular with the series overlap and the gapwidth:

co.Chart.ChartGroups(1).Overlap = -15
co.Chart.ChartGroups(1).GapWidth = 250

i don't know if this is of any help for the legend
i tried co.Chart.ChartGroups(1).HasLegend = False but i get error 438 "object does not support this property or method"


----

btw, it's the first time i see cell reference as ActiveSheet.[A22] . Does this apply to all versions of Excel (it surely works for 2010)?
I've been using .Cells(22, "A") or . Range("A22")

 

[Worf]

- The squares seem to be part of the data table. Try this:

co.Chart.SetElement msoElementDataTableShow     ' without legend keys

- I suppose the [A22] style applies for all recent versions.

 

[Dimitris254]

perfect, it worked indeed

thank you once again and enjoy the Olympics!

 

[Dimitris254]

btw is possible to edit the legend colors (and match the actual series' colors) ?