Color Chart Columns by Cell Color Error

Question

I am trying create a chart using cell colors from the active cells on a specific worksheet. When using the macro provided below I find that only some of the assigned cell RGB color codes match to the chart. I am not sure why some colors would match and some would not. The correct colors display in the chart when I manually enter the color codes. Is there something I am leaving out of this macro or an extra step I need to take?

I am using Excel 2016 for this project.

Sub ColorChartColumnsbyCellColor()
With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1)
Set vAddress = ActiveSheet.Range(Split(Split(.Formula, ",")(1), "!")(1))
For i = 1 To vAddress.Cells.Count
.Points(i).Format.Fill.ForeColor.RGB = 
ThisWorkbook.Colors(vAddress.Cells(i).Interior.ColorIndex)
Next i
End With
End Sub

Your problem comes from this line Set vAddress = ActiveSheet.Range(Split(Split(.Formula, ",")(1), "!")(1)) . Unfortunately, I don't know what you're trying to do with that nested split so I don't know how to help you. Try to Assert that line to see if you're getting the expected result. — Fernando J. Rivera
If any of the colors you're trying to pick up from the worksheet are coming from conditional formatting then you need to look at DisplayFormat.Interior.Color Those colors will not be reflected in Cells(i).Interior.ColorIndex - that will return the "base/resting" color of the cell without any CF changes applied — Tim Williams

Excelosaurus Excelosaurus · Accepted Answer · 2017-11-10T21:14:15

You're assigning a color index to the RGB property. Color indexes have nothing to do with Red Green Blue. Besides, @Tim William's has a point: conditional formatting may play a role in what you're doing.

Try this code, which assigns the Color property to the RGB property:

Sub ColorChartColumnsbyCellColor()
    With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1)
        Set vAddress = ActiveSheet.Range(Split(Split(.Formula, ",")(1), "!")(1))

        For i = 1 To vAddress.Cells.Count
            'Comment the line below and uncomment the next one to take conditional formatting into account.
            .Points(i).Format.Fill.ForeColor.RGB = vAddress.Cells(i).Interior.Color
            '.Points(i).Format.Fill.ForeColor.RGB = vAddress.Cells(i).DisplayFormat.Interior.Color
        Next i
    End With
End Sub

Color Chart Columns by Cell Color Error

1 Answers