Solved: Charts and Legends

[Lina]

Hi

Please may I ask, is it possible to set the colors in the legend to match the colors set for a cell.

I have a table that has 14 columns. The first is a list of names, the second a list of codes for the names, the following columns contain ratings are values assigned to each name. Each Column has been assigned a color. This is done on a monthly basis.

I need to create a chart to reflect the values/ratings found in the cells, as well as the colors for each rating.

Is it possible to do this.

Thank you
Lina

[Bob Phillips]

It can be done, but it would need VBA. Is that an acceptable solution?

[Lina]

Hi

Yes this is acceptable, may I ask you to show me how to do this.

Thank you
Lina

[Bob Phillips]

This code looks through each series and fines the name in row 1 of the data and extracts that text colour and sets the series line colour to it (I am assuming a line chart, called myChart)



Dim colNum As Long
Dim i As Long

With ActiveSheet.ChartObjects("myChart").Chart

For i = 1 To .SeriesCollection.Count

colNum = Application.Match(.SeriesCollection(i).Name, ActiveSheet.Rows(1), 0)
.SeriesCollection(i).Border.ColorIndex = ActiveSheet.Cells(1, colNum).Font.ColorIndex
Next i
End With

[Lina]

Thank you very much.

That helps a lot.

Lina:)