Excel Conditional Formatting Data Bars based on Color

Question

I cannot find a way to change the color of an Excel data bar based on value. Current formatting options only permit different colors based on positive/negative values. I'm currently using Excel 2010.

I would like to have the color of a data bar show up as 'red' if the value if between 0-0.3, 'yellow' if the value is between 0.3-0.6, and 'green' if the value if between >0.6.

Would really appreciate any info people could share.

Thanks,

TB

Amadeus

I found a website that has achieved something similar, although I can't quite get the VBA code to work. Perhaps someone with more experience can take a look? – Amadeus

digimac.wordpress.com/2014/06/29/… – Amadeus

The first thing you would do is to add some red data bars to your data, and then some green data bars. By default, Excel shows you the last set applied, so the data bars would be green. If you then launch the VB Editor (Alt + F11) and in the immediate window (Ctrl+G), type: selection.FormatConditions(1).formula = “=if(c3>59, true, false)” – Amadeus

When I try this solution, I get the following error: Compile error: Expected: expression – Amadeus

I'm the author of the addon that Blake found. I will try to make time to do some debugging. It works on my system so I just have to figure out what's stopping it for most other people.  – Gutturalize

Answer 1

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.

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

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.

Rand

Thanks for this tip! While not exactly what I'm looking for, its 90% there. A quick question, are you sure the unicode is 2588? When I enter in this unicode it comes up as little boxes, similar to your first example. I'm not sure how to make it look like a bar graph as per your second picture – Amadeus

Sorry just a clarification, unicode 2558 is coming up as small 'blocks', while unicode 2588 is actually coming up as an small L (but with both lines of the L equal length) – Amadeus

Sorry, I just realised that the 2588 unicode does not show the "block" character with any font. But it does with Arial. so I edited my post. – Rand

Nice technique but doesn't help if you want colour/completion to be independent e.g. you want to show task completion plus RAG status – Unalloyed

@adolfgarlic Well, that wasn't the question, was it? But it can still be done. Use a CF rule that looks at another cell for the value that determines the color. – Rand

Answer 2

I setup conditional formatting in the cell adjacent to the data bar that changes color based on the value in the target cell (green, yellow, red, orange). I then loop through the VBA below to update the data bar color based on the conditional formatting in the adjacent cell.

Dim intCount As Integer
Dim db As DataBar

On Error Resume Next
For intCount = 9 To 43 'rows with data bars to be updated
    Worksheets("Worksheet Name").Cells(intCount, 10).FormatConditions(1).BarColor.Color = Worksheets("Worksheet Name").Cells(intCount, 11).DisplayFormat.Interior.Color
Next intCount

Pinafore

Answer 3

In your case, highlight the cell will be more suitable as we can not form data bar with multiple colors
Conditional Formationg >Manage Rules...>New Rule
Under Select a Rule Type, choose "Use a formula to determince which cells to format" and set your rules there

Impatience

Answer 4

Instead of creating conditional formatting for a range of cells, I conditionally formatted each cell individually using VBA based on the two subs below. The result is shown in the link below the code. Hope this helps.

' The purpose of this sub is to add a data bar to an individual cell
' The value in the cell is expected to be decimal numbers between -1 and 1
' If the value is greater than or equal to -0.1 and less than or equal to 0.1, then display green bars
' If the value is less than -0.1 and greater than -.2, OR greater than 0.1 and less than 0.2 then yellow bars
' All other scenarios display red bars
Sub Add_Data_Bar(rngCell As Range, dblValue As Double)

' Clears existing conditional formatting from the cell
' Adds a new data bar to the cell
With rngCell.FormatConditions
    .Delete
    .AddDatabar
End With

    ' Creates a databar object for the databar that has been added to the cell
    Dim dbar As Databar
    Set dbar = rngCell.FormatConditions(rngCell.FormatConditions.Count)

        ' Sets the databar fill type to display as gradient
        dbar.BarFillType = xlDataBarFillGradient

        ' Sets the databar border style
        dbar.BarBorder.Type = xlDataBarBorderSolid

        ' Sets the databar axis position
        dbar.AxisPosition = xlDataBarAxisMidpoint

        ' Sets the minimum limit of the data bar to -1
        With dbar.MinPoint
            .Modify newtype:=xlConditionValueNumber, newvalue:=-1
        End With

        ' Sets the maximum limit of the data bar to +1
        With dbar.MaxPoint
            .Modify newtype:=xlConditionValueNumber, newvalue:=1
        End With

            ' Sets the color based on what value has been passed to the sub
            ' Green
            If dblValue <= 0.1 And dblValue >= -0.1 Then

                With dbar
                    .BarColor.Color = RGB(99, 195, 132) ' Green
                    .BarBorder.Color.Color = RGB(99, 195, 132)
                End With

            ' Yellow
            ElseIf (dblValue > 0.1 And dblValue <= 0.2) Or (dblValue < -0.1 And dblValue >= -0.2) Then

                With dbar
                    .BarColor.Color = RGB(255, 182, 40) ' Yellow
                    .BarBorder.Color.Color = RGB(255, 182, 40)
                End With

            ' Red
            Else

                With dbar
                    .BarColor.Color = RGB(255, 0, 0) ' Red
                    .BarBorder.Color.Color = RGB(255, 0, 0)
                End With

            End If

End Sub


' Applies the databar formatting to each cell in a range
‘ Call this on the Worksheet_Change event so that the formatting updates when data is refreshed
Sub Loop_Through_Range()

' Range to be looped through
Dim rng As Range
Set rng = Sheet1.Range("A2:A22")

' Range for For Loop
Dim cell As Range

    ' Loops through each cell in your range
    For Each cell In rng.Cells
        Call Add_Data_Bar(cell, cell.Value)
    Next

End Sub

Worksheet View

Nerta

Answer 5

This website provides a solution, albeit a clunky one, plus it requires no VBA coding - https://www.exceldemy.com/excel-bar-chart-change-color-based-on-category/. Scroll down to this section: 3. Bar Chart Color Changing Based on Formula Category.

The solution involves an IF statement which puts N/A into the FALSE option like this: =IF(C5<=$B$14, C5, NA()). You can instead use "" like this: =IF(C5<=$B$14, C5, ""). The chart will work out. You'll definitely want to do the last part of this step where you adjust the series overlap and gap.

Moy