VBA for chart line color and style by cell contents

poolio

New Member
Joined
Oct 8, 2010
Messages
43
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
 

Attachments

  • Chart Image.JPG
    Chart Image.JPG
    91.6 KB · Views: 8
  • Original Data.JPG
    Original Data.JPG
    52.8 KB · Views: 8

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

poolio

New Member
Joined
Oct 8, 2010
Messages
43
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

MrExcel MVP
Joined
May 14, 2003
Messages
5,157
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Lots of unneeded default code in your red sample.

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

VBA Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,241
Messages
5,836,193
Members
430,406
Latest member
pmav

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top