Chart Series names and colors

I have a macro in Excel 2007 that graphs a standard set of data automatically.  The graphing portion works perfectly, but I have some issues with choosing the colors of the series, and also the names of the series.  The current code I have works (See attached), but is not very elegant and I would like to get it to work better.  Originally I tried using the named ranges I have set up, but was unable to get two columns in the series name this way.  The original code was something like this

ActiveChart.SeriesCollection(Count).Border.Color = strColor & Count
ActiveChart.SeriesCollection(Count).Name = "='" & Sheet1.Name & "'!Product" & Count

The series name code worked fine, but the color code would not work (regardless of where I included quotes.  I tried to change the series name code to include two columns by doing this

ActiveChart.SeriesCollection(Count).Name = "='" & Sheet1.Name & "'!Product" & Count & ":Dosage" & Count

But it did not work.  I would like to figure out how to do this in a loop instead of a select case statement.

Also, using two columns in the series name places the two value right next to each other so they run together pretty badly (the last number in the image is from the second cell)...is there a way to separate them so that they read better?
Dim strColor1 As String
    Dim strColor2 As String
    Dim strColor3 As String
    Dim strColor4 As String
    Dim strColor5 As String
    Dim strColor6 As String
    Dim strColor7 As String
    Dim strColor8 As String
    Dim strColor As Variant
    
    strColor1 = RGB(0, 0, 0)
    strColor2 = RGB(255, 0, 0)
    strColor3 = RGB(0, 0, 255)
    strColor4 = RGB(0, 128, 0)
    strColor5 = RGB(255, 0, 102)
    strColor6 = RGB(102, 0, 102)
    strColor7 = RGB(255, 153, 0)
    strColor8 = RGB(102, 51, 0)
    
    Count = 1
    Set MyRange = Range("C2")
    Set xRange = Range("A3:A" & intExpectedRows)
    Set yRange = Range("C3:C" & intExpectedRows)
    
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    
    For Count = 1 To Sheet1.Range("CellCount").Value

    ActiveChart.SeriesCollection(Count).XValues = xRange
    ActiveChart.SeriesCollection(Count).Values = yRange
    Select Case Count
    Case 1
        ActiveChart.SeriesCollection(Count).Border.Color = strColor1
        ActiveChart.SeriesCollection(Count).Name = "='" & Sheet1.Name & "'!$B$19:$C$19"
    Case 2
        ActiveChart.SeriesCollection(Count).Border.Color = strColor2
        ActiveChart.SeriesCollection(Count).Name = "='" & Sheet1.Name & "'!$B$20:$C$20"
    Case 3
        ActiveChart.SeriesCollection(Count).Border.Color = strColor3
        ActiveChart.SeriesCollection(Count).Name = "='" & Sheet1.Name & "'!$B$21:$C$21"
    Case 4
        ActiveChart.SeriesCollection(Count).Border.Color = strColor4
        ActiveChart.SeriesCollection(Count).Name = "='" & Sheet1.Name & "'!$B$22:$C$22"
    Case 5
        ActiveChart.SeriesCollection(Count).Border.Color = strColor5
        ActiveChart.SeriesCollection(Count).Name = "='" & Sheet1.Name & "'!$B$23:$C$23"
    Case 6
        ActiveChart.SeriesCollection(Count).Border.Color = strColor6
        ActiveChart.SeriesCollection(Count).Name = "='" & Sheet1.Name & "'!$B$24:$C$24"
    Case 7
        ActiveChart.SeriesCollection(Count).Border.Color = strColor7
        ActiveChart.SeriesCollection(Count).Name = "='" & Sheet1.Name & "'!$B$25:$C$25"
    Case 8
        ActiveChart.SeriesCollection(Count).Border.Color = strColor8
        ActiveChart.SeriesCollection(Count).Name = "='" & Sheet1.Name & "'!$B$26:$C$26"
    Case Else
        ActiveChart.SeriesCollection(Count).Border.Color = strColor1
        ActiveChart.SeriesCollection(Count).Name = "='" & Sheet1.Name & "'!$B$27:$C$27"
    End Select

    Set MyRange = MyRange.Offset(0, 1)
    Set yRange = yRange.Offset(0, 1)
    Set NameRange = NameRange.Offset(0, 1)

    Next Count

Open in new window

Experts-Exchange-Question.bmp
uwlchemistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Use an array as below, then you can use:

ActiveChart.SeriesCollection(Count).Border.Color = alngColor(Count)
ActiveChart.SeriesCollection(Count).Name = "='" & Sheet1.Name & "'!Product" & Count

Dim alngColor(1 to 8) As Long
    alngColor(1) = RGB(0, 0, 0)
    alngColor(2) = RGB(255, 0, 0)
    alngColor(3) = RGB(0, 0, 255)
    alngColor(4) = RGB(0, 128, 0)
    alngColor(5) = RGB(255, 0, 102)
    alngColor(6) = RGB(102, 0, 102)
    alngColor(7) = RGB(255, 153, 0)
    alngColor(8) = RGB(102, 51, 0)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
uwlchemistAuthor Commented:
The array for the color works perfectly.  Thank you.  Just in case anyone else reads this thread, I fixed the other half of my question as well.  I used an intermediary cell to concatenate the legend entry I wanted, and then defined the legend entry usign that cell.  The code for definign the intermediary cell looks like this

strCellFormula = "='" & Sheet1.Name & "'!Product" & Count & " & " & Chr(34) & " - " & Chr(34) & "& Dosage" & Count & " & " & Chr(34) & "ppm" & Chr(34)

I defined the intermediary cells as "ChemicalName" & Count.  Then I just looped through using this code:

ActiveChart.SeriesCollection(Count).Border.Color = alngColor(Count)
ActiveChart.SeriesCollection(Count).Name = "='" & Sheet2.Name & "'!ChemicalName" & Count

Now everything works like a charm.  Thanks for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.