excelvbacharts

Set Color codes to the legend text in vba?

Here we picked up one old excel file with a chart generated by one lost vba dated years back. Obviously, the following trial (from this post) is not the expected solution.

    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 = RGB(0, 176, 80) ' Green
                End If
            Next
        End If
    Next

since it only changes the markers as shown here.

enter image description here

Has anyone used this function and remember how to set it?

enter image description here

Any suggestion to change the legend text color is appreciated.

Solution

  • Try this loop, instead of the one in your sample code. It worked on my simple chart..

    For Each chObj In ActiveSheet.ChartObjects
        For Each legentry In chObj.Chart.Legend.LegendEntries
            legentry.Format.TextFrame2.TextRange.Font.Fill.ForeColor = _
                    legentry.LegendKey.Format.Line.ForeColor
        Next
Next