Changing chart colors based on cell values

Question

I have a chart with 2 data series that reference cell values with conditional formatting to determine its color. I looked up a VBA code on the net to use on the chart to update the colors of the series based on the cell color, but when running the code nothing happens.

Anybody can help me with this?

This is the Chart

Sub CellColorsToChart()
'Updateby Extendoffice
Dim xChart As Chart
Dim I As Long, J As Long
Dim xRowsOrCols As Long, xSCount As Long
Dim xRg As Range, xCell As Range
On Error Resume Next
Set xChart = ActiveSheet.ChartObjects("Chart 2").Chart
If xChart Is Nothing Then Exit Sub
xSCount = xChart.SeriesCollection.Count
For I = 1 To xSCount
    J = 1
    With xChart.SeriesCollection(I)
        Set xRg = ActiveSheet.Range(Split(Split(.Formula, ",")(2), "!")(1))
        If xSCount > 4 Then
        xRowsOrCols = xRg.Columns.Count
        Else
        xRowsOrCols = xRg.Rows.Count
        End If
        For Each xCell In xRg
            .Points(J).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(xCell.Interior.ColorIndex)
            .Points(J).Format.Line.ForeColor.RGB = ThisWorkbook.Colors(xCell.Interior.ColorIndex)
            J = J + 1
        Next
    End With
Next
End Sub

chris neilsen chris neilsen · Accepted Answer · 2018-08-06T23:36:16

xCell.Interior references the cells applied color, not a conditional format.

If you are using Excel 2010+ then you should use .DisplayFormat to get the conditional format color.

Also, why bother with ThisWorkbook.Colorsand ColorIndex, just reference the color:

.Points(J).Format.Fill.ForeColor.RGB = xCell.DisplayFormat.Interior.Color

Also, after you set On Error Resume Next to trap an error on Set xChart =, you must reset the error handling

On Error Resume Next
    Set xChart = ActiveSheet.ChartObjects("Chart 2").Chart
On Error GoTo 0 ' <~~ reset error handling
If xChart Is Nothing Then Exit Sub

Changing chart colors based on cell values

1 Answers