VBA for chart line color and style by cell contents

[poolio]

I have a leaderboard representing sports teams that are automatically ranked into positional order 1 to 20 (see image).
This ranked order is also used as the legend (see image).

I display the data on a line chart as rank v date, which shows their positional change in fortune over the past 6 weeks.

No here is the problem:

The color of the line for the team ranked in position 1 is red, team 2 is blue, team 3 is green etc...
(Foolishly?) I manually colored all of the lines to represent their teams kit color a few weeks back, however as the teams have subsequently changed positions, they have adopted the colored line for that rank.

I have experimented to fix the data in specific rows on a spreadsheet, which works, but I'd like the ranked order to display in the legend and to coincide with the data lines (see image).

So I was wondering is there a way to use VBA to assign the color to a line based on the contents of a cell - I could manipulate the cells to contain text / number / icolor value etc. using a VLOOKUP.

I am able to compose some very clumsy text to re-color all of the cells by concatenating a load of text and pasting it into a macro, but I'd like a more elegant solution, say a macro triggered by a button to recolor the lines based on the contents of a range 20 cells.

Thanks in advance for any suggestions.

Poolio x

 

[Jon Peltier]

Here's an oldie but goodie:
VBA Conditional Formatting of Charts by Series Name

 

[poolio]

Jon,

Thanks for the link... Hoping that I can manipulate my code (excerpt below in red) to correspond to the various phrases in the code (bottom blue).
I recorded a Macro and chopped it into phrases that allowed me to produce the following code, which looped on for the twenty repeats, one per line (or ActiveChart.FullSeriesCollection(3) to (22) )

Sub ReColorLines()

' ReColorLines Macro

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.FullSeriesCollection(3).Select
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSolid
.ForeColor.RGB = RGB(0,102,204)
.BackColor.RGB = RGB(255,255,255)
.Transparency = 0
End With
ActiveChart.FullSeriesCollection(4).Select
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSolid
.ForeColor.RGB = RGB(255,0,0)
.BackColor.RGB = RGB(255,255,255)
.Transparency = 0
End With
ActiveChart.FullSeriesCollection(5).Select
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSolid
.ForeColor.RGB = RGB(153,204,255)
.BackColor.RGB = RGB(255,255,255)
.Transparency = 0
End With
etc... to (22)

Assume I will need to substitute the rPatterns Range with the appropriate cells...will have a play and see what I come up with, I'll be back for more probably

Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range

Set rPatterns = ActiveSheet.Range("A1:A4")
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
LookAt:=xlWhole)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = _
rSeries.Interior.Color
End If
Next
End With
End Sub

 

[Jon Peltier]

Lots of unneeded default code in your red sample.

You could do it something like this, without using the worksheet range.

Sub ColorActiveChartLines()
  With ActiveChart
    Dim srs As Series
    For Each srs In .SeriesCollection
      Select Case srs.Name
        Case "Alpha"
          srs.Format.Line.ForeColor.RGB = RGB(0, 102, 204)
        Case "Beta"
          srs.Format.Line.ForeColor.RGB = RGB(255, 0, 0)
        Case "Gamma"
          srs.Format.Line.ForeColor.RGB = RGB(153, 204, 255)
        ' etc.
      End Select
    Next
  End With
End Sub