Defining chart colours by cell entries

**banana-pumpkin** · 08-25-2010, 05:51 AM

I have some VBA code that I am using to change the background colour of cells in my column A using values in columns B,C,D to define the RGB components of the colour.
e.g. Red | 255 | 0 | 0 turns the first cell's background to red.

I've created a column chart using the column A values as x-values and some other data in column E as y-values. What I want is to make the fill colour of the columns in the chart the same as the background colour of the cells in the worksheet.

Please see attached file - thanks in advance for your help

**Andy Pope** · 08-25-2010, 08:08 AM

It could be that using dummy series and formula is a better approach. Hard to tell with such a contrived example and no explanation of actual usage.

Anyway here is some code.

Sub ColourChart()

    Dim chtTemp As Chart
    Dim objSeries As Series
    Dim rngAxisLabels As Range
    Dim lngIndex As Long
    
    Set chtTemp = ActiveSheet.ChartObjects(1).Chart
    Set rngAxisLabels = Range(Split(chtTemp.SeriesCollection(1).Formula, ",")(1))
    
    Set objSeries = chtTemp.SeriesCollection(1)
    lngIndex = 0
    For Each rngcell In rngAxisLabels
        lngIndex = lngIndex + 1
        objSeries.Points(lngIndex).Format.Fill.ForeColor.RGB = rngcell.Interior.Color
    Next
    
End Sub

I have used a very quick and dirty method of getting the range used for axis labels. In real-world usage you may need something more robust to extract the parameter.

**banana-pumpkin** · 08-25-2010, 08:24 AM

Thanks, Andy - I'll have a play with that and see if I can get it to work.

It's not actually a contrived example; I work for a paint company so the real usage is exactly the same, just with more colours and "Chart Value" replaced with our profit figures.