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

conditionalexcelformattingvba

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!!

Best Answer

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

Related Solutions

Excel – How to programmatically change the line colour for a series in a chart in Excel 2007

It is easy enough to colour a chart line in VBA. Here are some notes.

Dim cht  As Chart
Dim sc As Series
Dim blnBad As Boolean
Dim j

j = 85 'RGB orange '
blnBad = False

'This is a chart called Chart 1, it would be possible '
'to use the charts collection '
Set cht = ActiveSheet.ChartObjects("Chart 1").Chart
'A chart is composed of series of data ... '
For Each sc In cht.SeriesCollection
    ' ... that you can iterate through to pick up '
    ' the individual data values, or a data range. '
    ' Values in this case. '
    For i = LBound(sc.Values) To UBound(sc.Values)
        ' That can be checked against another set of '
        ' values in the range Bad. '
        With ActiveSheet.Range("Bad")
            ' So, look for the value ... '
            Set c = .Find(sc.Values(i), lookat:=xlWhole, LookIn:=xlValues)
            ' and if it is found ... '
            If Not c Is Nothing Then
                ' ... then set the Bad flag '
                blnBad = True
            End If
        End With
    Next
    ' So, this range contains a Bad value '
    ' and we will colour it red ... '
    If blnBad Then
        sc.Border.Color = RGB(255, 0, 0)
        ' ... not forgetting the markers '
        sc.MarkerForegroundColor = RGB(255, 0, 0)
    Else
        ' Otherwise, use an increasingly yellow colour '
        sc.Border.Color = RGB(255, j, 0)
        sc.MarkerForegroundColor = RGB(255, j, 0)

        j = j + 30 ' getting more yellow
        ' Debug.Print j ' uncomment to see j in the immediate window '
    End If
    blnBad = False
Next
End Sub

Excel Conditional Formatting Data Bars based on Color

Data bars only support one color per set. The idea is that the length of the data bar gives you an indication of high, medium or low.

Conditional colors can be achieved with color scales.

What you describe sounds like a combination of the two, but that does not exist in Excel and I don't see an easy way to hack it.

You could use a kind of in-cell "chart" that was popular before sparklines came along. Use a formula to repeat a character (in the screenshot it's the character g formatted with Marlett font), and then use conditional formatting to change the font color.

enter image description here

For a nicer "bar" feel, use unicode character 2588 with a regular font.

enter image description here

Edit: Not every Unicode character is represented in every font. In this case the the unicode 2588 shows fine with Arial font but not with Excel's default Calibri. Select your fonts accordingly. The Insert > Symbol dialog will help find suitable characters.

enter image description here

Best Answer

Related Solutions

Excel – How to programmatically change the line colour for a series in a chart in Excel 2007

Excel Conditional Formatting Data Bars based on Color

Related Topic