Changing bar colors in bar graph
Solution 1
Have you tried
.Chart.SeriesCollection([index]).Interior.Color = RGB(155, 187, 89)
(where [index] is a placeholder for the series you want to change the color for)?
Solution 2
It works for me ScottyStyles in a very similar situation, but only for the first series collection. I used the same right below that, and that was not changing the color of the SeriesCollection(2). That one is a linear set of datas.
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ClearToMatchStyle
ActiveChart.SeriesCollection(1).Interior.Color = RGB(85, 142, 213)
ActiveChart.SeriesCollection(2).Interior.Color = RGB(192, 0, 0)
Solution 3
to change different bars inside a collection you can use:
ActiveChart.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(85, 142, 213)
ActiveChart.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = RGB(192,0, 0)
...
ScottyStyles
Updated on July 09, 2022Comments
-
ScottyStyles almost 2 years
I've created a VBA for Excel 2007 program that automatically creates bar graphs for ROI based on up to 52 different tabs in the active workbook. I'm close to done, and the only thing I cannot figure out is how to change the colors of the bargraphs.
The graphs are created in their own subfunction, called with a call like so. Every variable changes around whenever it's called.
Call AddChartObject(1, 1, "Example", extraWeeks, weekDifference)
My sub that it calls looks like this.
Sub AddChartObject(j As Integer, k As Integer, passedChartTitle As String, xtraWks As Integer, ttlWks As Integer) Dim topOfChart As Integer topOfChart = 25 + (350 * j) 'Adds bar chart for total sales With ActiveSheet.ChartObjects.Add(Left:=375, Width:=475, Top:=topOfChart, Height:=325) .Chart.SetSourceData Source:=Sheets("Consolidation").Range("$A$" & 3 + ((17 + xtraWks) _ * j) & ":$C$" & (4 + ttlWks) + ((17 + xtraWks) * k)) .Chart.ChartType = xl3DColumnClustered .Chart.SetElement (msoElementDataLabelShow) .Chart.HasTitle = True .Chart.ChartTitle.Text = passedChartTitle & " Sales" .Chart.SetElement (msoElementLegendBottom) .Chart.SetElement (msoElementDataLabelNone) .Chart.RightAngleAxes = True End With End Sub
The RGB color I want to use on the SECOND series in the bar chart is (155, 187, 89), per marketing's wishes. I'm pretty sure there is a
.chart.????.???? = RGB (155, 187, 89)
command I can use in myWith
to set this, but I have spent far too much time trying to figure it out, only to come up with nothing. -
ScottyStyles about 13 yearsPerfect! I was just missing the .interior. portion. Thanks a ton Jubbles!