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

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.

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