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

, GREEN

or YELLOW

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()
        ActiveSheet.Shapes.AddChart.Select
        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
        ActiveChart.Legend.Select
        Selection.Delete
        ActiveSheet.ChartObjects("Chart 3").Activate
        ActiveChart.ChartTitle.Select
        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!

+3


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()
  FormatGraph("Overview")
End Sub

      



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

Function FormatGraph(myWorksheet As String)

Excel.Sheets(myWorksheet).Select

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

    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
        Next
    End With

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

End Function

      

+2


source


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.

+2


source







All Articles