Home > OS >  How to set chart series color for entire series (all points)
How to set chart series color for entire series (all points)

Time:06-15

I'm trying to loop through the entire series to color all points (bars) in the chart pictured below using VBA. This is what I have so far

Sub ChartColors()
    Dim sld As Slide
    Dim shp As Shape
    Dim cht As Chart
    Dim cats As Variant
    Dim j As Integer

    For Each sld In ActivePresentation.Slides
        For Each shp In sld.Shapes
            If shp.Type = msoChart Then
                cats = shp.Chart.Axes(xlCategory).CategoryNames

                For j = LBound(cats) To UBound(cats)
                    With shp.Chart.SeriesCollection(1).Points(j).Format.Fill.ForeColor
                        Select Case cats(j)
                            Case "Apple"
                                .RGB = RGB(192, 0, 0)
                            Case "Banana"
                                .RGB = RGB(0, 112, 192)
                            Case Else
                                .RGB = RGB(0, 176, 80)
                        End Select
                    End With
                Next j
            End If
        Next shp
    Next sld
End Sub 

enter image description here

CodePudding user response:

Try the following code...

Option Explicit

Sub ChartColors()
    Dim sld As Slide
    Dim shp As Shape
    Dim cht As Chart
    Dim sr As Series
    Dim pt As Point
    Dim cats As Variant
    Dim i As Integer
    Dim j As Integer

    For Each sld In ActivePresentation.Slides
        For Each shp In sld.Shapes
            If shp.Type = msoChart Then
                Set cht = shp.Chart
                With cht
                    For i = 1 To .SeriesCollection.Count
                        Set sr = .SeriesCollection(i)
                        For j = 1 To sr.Points.Count
                            cats = sr.XValues()(j)
                            Select Case cats
                                Case "Apple"
                                    sr.Points(j).Format.Fill.ForeColor.RGB = RGB(192, 0, 0)
                                Case "Banana"
                                    sr.Points(j).Format.Fill.ForeColor.RGB = RGB(0, 112, 192)
                                Case Else
                                    sr.Points(j).Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
                            End Select
                        Next j
                    Next i
                End With
            End If
        Next shp
    Next sld
End Sub
  • Related