Change colors of all bar chart categories for all bar charts (VBA)?

Question

I have about 9 bar charts that I've rank ordered. Problem is, when you do that, the name-colors are different across the charts.

So I would like to make the color for each of my rows the same across all the charts.

This code runs but it doesn't do anything and I'm not quite sure why? Is series the right thing to be changing? I only have two of them here for an example, there are 8 ifs in the actual thing.

Sub Button3_Click()

For Each myChartObject In Sheets("Rank Calc").ChartObjects
    For Each mySrs In myChartObject.Chart.SeriesCollection
        If mySrs.Name = "Monarch" Then mySrs.Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
        If mySrs.Name = "Alpha" Then mySrs.Format.Fill.ForeColor.RGB = RGB(0, 255, 0)

    Next
Next
End Sub

I think series might be the wrong thing, but my data is laid out with all the names in one column and then their values in the column next to them, if that helps determine if "series" is the right thing to be changing the color of.

I've also now tried this:

Sub Button3_Click()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rCategory As Range

Set rPatterns = Sheets("RankCalc").Range("AH1:AH8")
For Each myChartObject In Sheets("RankCalc").ChartObjects
    With myChartObject.SeriesCollection(1)
      vCategories = .XValues
      For iCategory = 1 To UBound(vCategories)
        Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
        .Points(iCategory).Format.Fill.ForeColor.RGB = rCategory.Interior.Color
      Next
    End With
    Next
End Sub

But it tells me that the With myChartObject.SeriesCollection(1), that object doesn't support that method. So I don't know what to do.

I would have set a template with associated chart and pasted the data into a set region so that all the colors were set, in fact, you could automatically call the data in order from a generic range by using the large() function... — Solar Mike
Problem is that the category names are always in a different order, so that the charts will display them largest-->smallest. I need the names to always be the same color though. — CapnShanty

WhiteHat WhiteHat · Accepted Answer · 2018-11-30T19:18:10

to change the color of a series,
set property --> .Interior.Color

see following snippet...

Sub Button3_Click()
  Dim myChartObject As ChartObject
  Dim mySrs As Series

  For Each myChartObject In Sheets("Sheet1").ChartObjects
    For Each mySrs In myChartObject.Chart.SeriesCollection
      Select Case mySrs.Name
        Case "A"
          mySrs.Interior.Color = RGB(255, 0, 0)
        Case "B"
          mySrs.Interior.Color = RGB(0, 255, 0)
        Case "C"
          mySrs.Interior.Color = RGB(0, 0, 255)
        Case "D"
          mySrs.Interior.Color = RGB(255, 255, 0)
        Case "E"
          mySrs.Interior.Color = RGB(0, 0, 0)
      End Select
    Next
  Next
End Sub

Change colors of all bar chart categories for all bar charts (VBA)?

1 Answers