Conditionally change colors of individual bars on a histogram in Excel VB based on a range

I am working on an Excel project and am trying to format the colors of a bar chart (and then a pie chart for the same reasons) to display RED



based on a different range of data. Data range ...

Sheet :Overview

Range :E15:E36

These values ​​are percentages. Depending on what percentage it is in between, I would like the bars to be formatted green, red, or yellow.

If between 100 - 90, green If between 89 - 70, Yellow If between 69 - 1, Red

Below is my code to this point (for a histogram):

    Sub Macro2()
        ActiveChart.ChartType = xlColumnClustered
        ActiveChart.SetSourceData Source:=Sheets("Overview").Range("A15:A36")
        ActiveChart.SetSourceData Source:=Sheets("Overview").Range("A15:A36,B15:B36")
        ActiveChart.ApplyLayout (2)
        ActiveSheet.ChartObjects("Chart 3").Activate
        ActiveSheet.ChartObjects("Chart 3").Activate
        ActiveSheet.ChartObjects("Chart 3").Activate
        ActiveChart.ChartTitle.Text = "Rating Site Distribution"
    End Sub


Any help would be greatly appreciated! I am not quite familiar with VBA and totally feel out of my element on this ...

Also, would the same function work for a pie chart to determine color by the same parameters?

Thanks in advance!


source to share

2 answers

here's the vba function i use to invert negative bars so they are red. Perhaps this can be adapted:

The function is called from a subroutine in module a in the workbook like this:

Sub FixTheGraph()
End Sub


Here's the function. You can just paste it under the subroutine:

Function FormatGraph(myWorksheet As String)


Dim myChartObject As ChartObject
For Each myChartObject In Excel.ActiveSheet.ChartObjects

    Dim myPoint As Integer, valArray

    With Excel.ActiveChart.SeriesCollection(1)
        valArray = .Values
        For myPoint = 1 To .Points.Count
            'myVal = valArray(myPoint)
            Select Case valArray(myPoint)
                Case 0.9 To 1
                    With .Points(myPoint)
                        .Interior.ColorIndex = 1 '<change colour to suit
                    End With
                Case 0.7 To 0.9
                    With .Points(myPoint)
                        .Interior.ColorIndex = 5 '<change colour to suit
                    End With
                Case Is < 0.7
                    With .Points(myPoint)
                        .Interior.ColorIndex = 3
                    End With
                End Select
    End With

Next myChartObject
Excel.Sheets(myWorksheet).Cells(1, 1).Select

End Function




What you want to do is the xlBarStacked chart type. Then give him three batches - one for each color.

In each series, if the element value is in the range, use the value, otherwise set the series.Value parameter to 0.

Since your ranges are mutually exclusive, each bar will only have one color.



All Articles