Thread: Column color based on series name?

Hi,

So far I haven't found an answer to this. I have to reformat about 25 charts and I want the columns to be colored based on the series name so that it is the same in all the charts. I found this one macro, and it worked initially when I tested it, but then it just stopped and I can't get it to work again!? Basically, you are supposed to just create a set of color coded index cells.

Any help appreciated (or why this stupid macros might stop working).

Code:

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).Interior.ColorIndex = _
            rSeries.Interior.ColorIndex
      End If
    Next
  End With
End Sub

Please add code tags to your post.

Post workbook example where the code is not working.

Ok - after I fixed the code, the macros will run - until you save the sheet. When you reopen the sheet and try to run it gives the following error:

With ActiveChart.SeriesCollection(1)

Run Time Error 91
Object variable or with block variable not set

WTF?

I attached the worksheet with the macro.

When you reopen the workbook a cell would be active rather than a chart.
If you select the chart first then run the code does it work again?

Try using code that does not require the chart to be selected

Code:

  Set rPatterns = ActiveSheet.Range("A1:A2")
  With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
    vCategories = .XValues
    For iCategory = 1 To UBound(vCategories)
      Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
      .Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
    Next
  End With