Thread: Matching graph colours with legend error

Hi guys,

I've been having problems with trying to set certain colours to certain graphs within an excel chart and then matching that colour to the font of a legend. The run-time error 1004 appears when there is no graph to select.

See the code below for reference. So for example, say I have built this sheet to automate for 5 graphs (capacity) within the one chart. Say I only have 3 actual graphs, therefore the error will occur on the line of code:

ActiveChart.SeriesCollection(4).Select

because there is no graph 4.

Now adding to the beginning of the code an error handler

 on error resume next

deals with this problem, however while it skips the error in the above code, it does not skip the body of code next to it. In other words, because prior to the error, graph 3 was selected, it will continue with the 'workable' codes:

    With Selection.Border
        .ColorIndex = 7     'MAGENTA
    End With
Range("legenda").Offset(4, 0).Select
Selection.Font.ColorIndex = 7

    With Selection.Border
        .ColorIndex = 15     'GREY
    End With
Range("legenda").Offset(5, 0).Select
Selection.Font.ColorIndex = 15

Thus working its way all the way to the last line of code. So in this particular example with 3 available graphs out of a capacity of 5, the colour index of the third (and last available) graph will be

     With Selection.Border
        .ColorIndex = 15     'GREY

and not

    With Selection.Border
        .ColorIndex = 5     'BLUE

I hope I'm making sense. I would really appreciate it if anyone could come up with a solution or workaround please? Many many thanks in advance.

See below for the 'full code':

Sub private()

                    'GRAPH1
On Error Resume Next
ActiveSheet.ChartObjects("ChartA").Activate
ActiveChart.SeriesCollection(1).Select
    With Selection.Border
        .ColorIndex = 3     'RED
    End With
Range("legenda").Offset(1, 0).Select
Selection.Font.ColorIndex = 3



                    'GRAPH2

ActiveSheet.ChartObjects("ChartA").Activate
ActiveChart.SeriesCollection(2).Select
    With Selection.Border
        .ColorIndex = 4     'GREEN
    End With
Range("legenda").Offset(2, 0).Select
Selection.Font.ColorIndex = 4

                    
                    
                    'GRAPH3

ActiveSheet.ChartObjects("ChartA").Activate
ActiveChart.SeriesCollection(3).Select
    With Selection.Border
        .ColorIndex = 5     'BLUE
    End With
Range("legenda").Offset(3, 0).Select
Selection.Font.ColorIndex = 5



                    'GRAPH4

ActiveSheet.ChartObjects("ChartA").Activate
ActiveChart.SeriesCollection(4).Select
    With Selection.Border
        .ColorIndex = 7     'MAGENTA
    End With
Range("legenda").Offset(4, 0).Select
Selection.Font.ColorIndex = 7



                    'GRAPH5

ActiveSheet.ChartObjects("ChartA").Activate
ActiveChart.SeriesCollection(5).Select
    With Selection.Border
        .ColorIndex = 15     'GREY
    End With
Range("legenda").Offset(5, 0).Select
Selection.Font.ColorIndex = 15

Instead of coding the coloring separately, loop through each series:

Sub private1()

Dim arrColor(4) As Integer
arrColor(0) = 3
arrColor(1) = 4
arrColor(2) = 5
arrColor(3) = 7
arrColor(4) = 15

ActiveSheet.ChartObjects("ChartA").Activate

For i = 1 To ActiveChart.SeriesCollection.Count
    ActiveChart.SeriesCollection(i).Select
        With Selection.Border
            .ColorIndex = arrColor(i - 1)   'RED
        End With
    Range("legenda").Offset(i, 0).Select
    Selection.Font.ColorIndex = arrColor(i - 1)
Next i

End Sub

I don't understand. You say you have a variable number of graphs, but your code above all only works on one graph, ChartA. Are you talking about multiple charts, or multiple data series? Perhaps you could post a small sample workbook?

Don't select:

For i = 1 To ActiveChart.SeriesCollection.Count
        With ActiveChart.SeriesCollection(i).Border
            .ColorIndex = arrColor(i - 1)   'RED
        End With
    Range("legenda").Offset(i, 0).Font.ColorIndex = arrColor(i - 1)
Next i

D'oh! Thank you for the simply obvious solution romperstomper!

Sorry davegugg, each data series on a chart is technically a 'graph'. Sorry for the confusion and many thanks for your help.

Also, how do I mark this thread as solved please?

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save