VBA

A simple code to use is where the graphic should be selected and then the code is executed:

Sub ColorByValue()
'https://peltiertech.com/vba-conditional-formatting-of-charts-by-value/
'http://www.clearlyandsimply.com/clearly_and_simply/2011/08/color-coded-bar-charts-with-microsoft-excel.html

Dim rPatterns As Range
Dim iPattern As Long
Dim vPatterns As Variant
Dim iPoint As Long
Dim vValues As Variant
Dim rValue As Range

Set rPatterns = ActiveSheet.Range("A1:A4")
vPatterns = rPatterns.Value
With ActiveChart.SeriesCollection(1)
    vValues = .Values
        For j = LBound(vValues) To UBound(vValues)
        Debug.Print vValues(j)
        Next j
    For iPoint = 1 To UBound(vValues)
        For iPattern = 1 To UBound(vPatterns)
            If vValues(iPoint) <= vPatterns(iPattern, 1) Then
                .Points(iPoint).Format.Fill.ForeColor.RGB = _
                                                          rPatterns.Cells(iPattern, 1).Interior.Color
                Exit For
            End If
        Next
    Next
End With
End Sub

Where the boundary values to color the chart are placed in column A of the worksheet, for example: and this part of the code changed for the interval used: Set rPatterns = ActiveSheet.Range("A1:A4")

Each cell with the boundary value must have the color of the interior that should be used.

And the result:

Note:

Ifanerroroccurs,thedatamusthavetheinvertedcolumn:

+------+----+|2700|1||1100|2||3000|3||2100|4||3000|5||900|6||2900|7||600|8||300|9||1100|10||2000|11||2300|12||1700|13||2700|14||0|15|+------+----+

Orthispartofthecodechangedto:WithActiveChart.SeriesCollection(2)

ExcelFormula

YoudonotneedtouseVBAtodothis,itcanonlybedonewithformulas.BecausewithVBAit'sabitmorecomplexandthisisdonefasterandsimplerwiththeformulas.

Forexample,youhavethistablewithvalues:

+------+-------+|A|B|+------+-------+|1|2700||2|1100||3|3000||4|2100||5|3000||6|900||7|2900||8|600||9|300||10|1100||11|2000||12|2300||13|1700||14|2700||15|0|+------+-------+

Andyouwantthreeranges,oneforeachcolor:

• Red:IfcolumnBissmallerthan1000.
• Yellow:IfcolumnBisgreaterorequalthan1000andsmallerthan2000.
• Green:IfcolumnBisgreaterthanorequalto2000.

Therefore,threecolumnswillbeusednexttothevaluesincolumnBtocheckwhichvaluesarewithintheserangesandcreateadataseriesforeachnewcolumn.

Theformulasareasfollows:

• Forred:=SE(B2<1000;B2;"")
• For yellow: =SE(E(B2>=1000;B2<2000);B2;"")
• For green: =SE(B2>=2000;B2;"")

If it is within the range, the value is entered in the cell, otherwise the blank value "" is left.

Resulting in the following table:

+----+------+----------+---------+-------+
| A  |  B   | Vermelho | Amarelo | Verde |
+----+------+----------+---------+-------+
|  1 | 2700 |          |         |  2700 |
|  2 | 1100 |          |    1100 |       |
|  3 | 3000 |          |         |  3000 |
|  4 | 2100 |          |         |  2100 |
|  5 | 3000 |          |         |  3000 |
|  6 |  900 |      900 |         |       |
|  7 | 2900 |          |         |  2900 |
|  8 |  600 |      600 |         |       |
|  9 |  300 |      300 |         |       |
| 10 | 1100 |          |    1100 |       |
| 11 | 2000 |          |         |  2000 |
| 12 | 2300 |          |         |  2300 |
| 13 | 1700 |          |    1700 |       |
| 14 | 2700 |          |         |  2700 |
| 15 |    0 |        0 |         |       |
+----+------+----------+---------+-------+

With this, a graph can be inserted in: Inserir > Gráfico Coluna Agrupada and then go in Selecionar Dados .

Each new column should be added as a new data series:

Thedataaddedforeachseriesarethevaluesforeachcolumn:

Finally,eachdataseriesofthechartshouldbeselectedanditscolorchanged: