CODEWITHSUNDEEP
excelvba
Raven
Raven

Reputation: 11

Changing chart colors based on cell values

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

Upvotes: 0

Views: 1435

Answers (1)

chris neilsen
chris neilsen

Reputation: 53136

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

Upvotes: 1

Related Questions