Color of a chart from cells with its data

Formulation of the problem

I would like the columns on the histogram (or slices on the pie chart, etc.) to automatically have the color that was used to fill the corresponding cells with the source data:

Anticipating the surprised and indignant cries of individual comrades, it should be noted that, of course, the color of the fill in the diagram can also be changed manually (right-click on the column – Point/series format (Format data point/series) etc. – no one argues. But in practice, there are a lot of situations when it is easier and more convenient to do this directly in the cells with data, and then the chart should then be repainted automatically. Try, for example, to set the fill by region for the columns in this chart:

I think you get the idea, right?

Solution

Nothing but a macro can do this. Therefore, we open Visual Basic editor from the tab developer (Developer — Visual Basic Editor) or press the keyboard shortcut Alt + F11, insert a new empty module through the menu Insert – Module and copy the text of such a macro there, which will do all the work:

Sub SetChartColorsFromDataCells()        If TypeName(Selection) <> "ChartArea" Then          MsgBox "Сначала выделите диаграмму!"          Exit Sub      End If      Set c = ActiveChart      For j = 1 To c.SeriesCollection.Count          f = c.SeriesCollection(j).Formula          m = Split(f, ",")          Set r = Range(m(2))            For i = 1 To r.Cells.Count              c.SeriesCollection(j).Points(i).Format.Fill.ForeColor.RGB = _                  r.Cells(i).Interior.Color          Next i      Next j  End Sub  

You can now close Visual Basic and return to Excel. Using the created macro is very simple. Select the chart (chart area, not plot area, grid or columns!):

and run our macro with the button Macros tab developer (Developer — Macros) or with a keyboard shortcut Alt + F8. In the same window, in case of frequent use, you can assign a keyboard shortcut to the macro using the button Parameters (Options).

PS

The only fly in the ointment is the impossibility of using a similar function for cases where the color is assigned to the cells of the source data using conditional formatting rules. Unfortunately, Visual Basic does not have a built-in tool for reading these colors. There are, of course, certain “crutches”, but they do not work for all cases and not in all versions.

• What are macros, how to use them, where to insert macro code in Visual Basic
• Conditional Formatting in Excel 2007-2013
• What’s New in Charts in Excel 2013