Excel VBA Is there a way to change the bar colors for excel bar charts (just one color) for all bar-CodePudding

This isn't what I need (Change colors of all bar chart categories for all bar charts (VBA)?)

I have an excel with 500 charts. When I make changes to the workbook, oftentimes, the charts randomly lose their formatting. For example, I went through and established a color theme when I first created all of the charts. There are xlColumnClustered and there are line charts. The bars in the xlColumnClustered should be green but Excel keeps randomly changing them to blue.

Is there a way for me to use VBA to select only the xlColumnClustered charts in the sheet and change the color from blue to green for all the xlColumnClustered charts? (Don't want to impact any other chart types.) Also, want to change the line colors for the line charts from blue to green. Obviously, I am a beginner with VBA...

Sub test()

Worksheets("portfolio_charts").Activate

ActiveSheet.ChartObjects.ChartType("xlColumnClustered").Select

End Sub

For example, if the chart type is xlColumnClustered then change bar color to green for all xlColumnClustered charts in the worksheet.

CodePudding user response：

Loop over all charts of the worksheet (don't use Activate or Select). Check the chart type to decide if you want to do something:

Sub ChangeAllBarChars()
    Dim co As ChartObject
    For Each co In ThisWorkbook.Sheets("portfolio_charts").ChartObjects
        If co.Chart.ChartType = xlColumnClustered Then
            SetChartColor co.Chart
        End If
    Next
End Sub

No do your magic for the data series of that chart. The following code sets all bars to green, maybe not what you want - up to you to decide. The color RGB(0, 192, 0) is kind of green, make up your mind which exact color you want.
If you set ForeColor.RGB, the color will not change, even if the color scheme for the Excel file is changed. If you use ForeColor.ObjectThemeColor and ForeColor.TintAndShade, the color will change when the color scheme is changed.

Sub SetChartColor(ch As Chart)
    Dim ser As Series
    For Each ser In ch.FullSeriesCollection
        ser.Format.Fill.ForeColor.RGB = RGB(0, 192, 0)

        ' Use the following when you want to stick to the scheme
        ' ser.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6
        ' ser.Format.Fill.ForeColor.TintAndShade = 0.4

    Next
End Sub

CodePudding user response：

You did not answer my clarification questions, so I will show a way to color ChartArea and PlotArea:

Sub ColorChartsChartArea()
  Dim sh As Worksheet, ch As ChartObject
  
  Set sh = ThisWorkbook.Sheets("portfolio_charts")
  For Each ch In sh.ChartObjects
        If ch.Chart.ChartType = xlColumnStacked Then
            ch.Chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
        End If
  Next ch
End Sub