This next variation of the color-bars macro allows you to assign colors to the chart bars based on the background colors of selected cells. For example, assume the screenshot below:

The macro below loops through the department column and assigns a color to the bars based on that department.

Sub ColorBars()

    Application.ScreenUpdating = False

    Dim Rng As Range
    Dim Cnt As Integer
    Dim Color As Integer

    Cnt = 1

    For Each Rng In Range("B3:B10")
        Color = Rng.Interior.ColorIndex
        Set Pts = ActiveChart.SeriesCollection(1).Points(Cnt)
        Pts.Interior.ColorIndex = Color
        Cnt = Cnt + 1
    Next Rng

End Sub

The final product looks like the chart below.

Good Afternoon.  I found your site via MSDN today in reference to assigning colors to chart bars…

I have been able to get your code to work on a chart that is in the same sheet as the data table, but my need is to have a number of charts assign the same color code to a list of some 100 potential x-values (products).  the products do not appear on every chart, but I would like to have them always be the same color no matter what chart they are on.

I’m sure there must be a way to code this, but I’ve only found a handful of samples - yours works the best - but only within the same sheet.

I get a run-time error “unable to get the Points property of the Series class” whenever I try to update a ChartSheet or a worksheet that has multiple charts.  the data series are all referenced from a separate worksheet, however it is the same worksheet and I have color-coded the cells with the products on that worksheet.

Do you think you could point me in a good direction?
Thanks!

email missing…