CODEWITHSUNDEEP
excelbar-chart
shadow777
shadow777

Reputation: 27

Excel - coloured bars in bar chart based on value

I've got a table like below:

enter image description here

And chart made from this table:

enter image description here

Now i would like to format the color of bars in this chart based on whether the value is >=100% (then green color) or <100%.

Is there any way to do it?

Thanks in advance :)

Upvotes: 0

Views: 116

Answers (1)

Olly
Olly

Reputation: 7891

You could use VBA to reformat the bars, based on the values:

Sub BarChartConditionalFormat()
    Dim ser As Series
    Dim v() As Variant
    Dim i As Integer

    With ActiveSheet.ChartObjects("Chart 1").Chart
        For Each ser In .SeriesCollection
            v = ser.Values
            For i = LBound(v) To UBound(v)
                If v(i) >= 1 Then
                    ser.Points(i).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
                Else
                    ser.Points(i).Format.Fill.ForeColor.RGB = ser.Format.Fill.ForeColor
                End If
            Next i
        Next ser
    End With
End Sub

Upvotes: 1

Related Questions