VBA: Coloring chart series with "Select Case"

Question

I am pretty new to VBA thus some help on this is highly appreciated. :) As the question header shows I tried to give every serie in my chart a different color. What I got so far works well but with a small limitation. The names of the series changes sometimes so instead of changing the code every single time I would like excel to take the name right out of the cell. For example instead of writing "dormakaba" by hand the value is written in the worksheet cell "A2". Would be great if someone has a solution.

Sub Color()
 Dim iSrs As Long, nSrs As Long
 If ActiveChart Is Nothing Then
  MsgBox "No chart selected. Please try again.", vbExclamation, "No Active Chart"
 Else
  With ActiveChart
   nSrs = .SeriesCollection.Count
   For iSrs = 1 To nSrs
    ' only format series whose names are found
    Select Case LCase$(.SeriesCollection(iSrs).Name)

        Case "dormakaba" '******CHANGE HERE******
            .SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = RGB(255, 130, 171)
            .SeriesCollection(iSrs).Format.Line.Visible = True
            .SeriesCollection(iSrs).Format.Line.Visible = False
        Case "georg fischer" '******CHANGE HERE******
            .SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = RGB(155, 48, 255)
            .SeriesCollection(iSrs).Format.Line.Visible = True
            .SeriesCollection(iSrs).Format.Line.Visible = False
        Case "clariant" '******CHANGE HERE******
            .SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
            .SeriesCollection(iSrs).Format.Line.Visible = True
            .SeriesCollection(iSrs).Format.Line.Visible = False
        Case "givaudan" '******CHANGE HERE******
            .SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = RGB(202, 225, 255)
            .SeriesCollection(iSrs).Format.Line.Visible = True
            .SeriesCollection(iSrs).Format.Line.Visible = False
        Case "galencia" '******CHANGE HERE******
            .SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = RGB(67, 205, 128)
            .SeriesCollection(iSrs).Format.Line.Visible = True
            .SeriesCollection(iSrs).Format.Line.Visible = False
        Case "lonza" '******CHANGE HERE******
            .SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = RGB(238, 230, 133)
            .SeriesCollection(iSrs).Format.Line.Visible = True
            .SeriesCollection(iSrs).Format.Line.Visible = False

     End Select
    Next
   End With
  End If
 End Sub

Why are you using VBA for this? Wouldn't it be easier to just change the series chart colour the normal (interactive) way? You can create your own palletes in Excel so charts will automatically come out with these colours too... — Dan, Aug 07 '18 at 09:10

score 1 · Accepted Answer · answered Aug 07 '18 at 08:33

Change "dormakaba" with Range("A2") in your code:

Select Case LCase$(.SeriesCollection(iSrs).Name)

Case LCase$(Range("A2"))    '******CHANGE HERE******
    .SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = RGB(255, 130, 171)
    .SeriesCollection(iSrs).Format.Line.Visible = True
    .SeriesCollection(iSrs).Format.Line.Visible = False

As you are avoidning the case, then LCase$(Range("A2")) would be even better.

VBA: Coloring chart series with "Select Case"

1 Answers1