Set Color codes to the legends in vba

问题:

I have pivot tables in each worksheets and I have to compare them but the colors for the legends in each of the worksheet is different. How to I set the colors?

For example, If my legend entry is "ISO" I want it to be always "blue", if its "LAT" I want it to be "Red" in every sheet.

回答1:

This can be done by manipulating the Series objects in the SeriesCollection property of a Chart.

I've written a short example method below that takes a worksheet, a legend name and a target RGB color. It loops the sheet's shapes, and if they contain charts, looks for a Series with the specified legendName. If it fits, it changes the forecolor to the specified RGB color.

Private Sub FormatShapeLegend(sheet As Worksheet, legendName As String, targetColor As MsoRGBType)
    Dim shp As Shape
    Dim chrt As Chart
    Dim s As Series

    For Each shp In sheet.Shapes
        If shp.HasChart Then
            Set chrt = shp.Chart

            'Loop the dataseries to find the legend with the desired name.
            For Each s In chrt.SeriesCollection
                'If the name fits, go ahead and format the series.
                If LCase(s.Name) = LCase(legendName) Then
                    s.Format.Fill.ForeColor.RGB = targetColor
                End If
            Next
        End If
    Next
End Sub

Example usage:

FormatShapeLegend ActiveSheet, "ISO", RGB(0, 0, 255)