CODEWITHSUNDEEP
vbaexcelformattingconditional-statements
cupcakes
cupcakes

Reputation: 31

Conditionally change colors of individual bars in bar chart in Excel VB based on range

I am working on an Excel project and am trying to format the colors of a bar chart (and later a pie chart by the same reasoning) in order to display RED, GREEN, or YELLOW based on another range of data. The data range is...

Sheet: Overview

Range: E15:E36

These values are percentages. Based on what percentage it falls between, I'd 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 the bar chart):

    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'm not at all familiar with VBA and feel entirely out of my element on this one...

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

Thank in advance!!

Upvotes: 3

Views: 17236

Answers (2)

Marc Thibault
Marc Thibault

Reputation: 1728

What you want to do is a chart type xlBarStacked. Then give it three series - one for each color.

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

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

Upvotes: 2

whytheq
whytheq

Reputation: 35605

here a vba function I use to invert negative bars so they are red. Maybe this can be adapted:

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

Sub FixTheGraph()
  FormatGraph("Overview")
End Sub

Here's the Function. You can just paste it below the sub-routine:

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

Upvotes: 4

Related Questions