3-Color Data Bar Set Conditional Formatting

Question

Hi,

I am trying to set a conditional formatting rule that will show a data bar and also change colors based on the values I specify. Here's an example of what I want to do:

<14 = orange

14-18 = yellow

18+ = filled up bar and green

I can't find any way to do this. Any suggestions? If there's a way that doesn't use conditional formatting, I'd also love to hear those ideas.

Any advice appreciated,

Answer 1

The only way is to use a macro to set databar color individually.

Note: You will need to run macro again when data is updated.

Sub ApplyConditionalDataBars()

Dim cell As Range

Dim dataBar As dataBar

' Loop through each cell in the range A2:A10

For Each cell In Range("A2:A10")

    ' Clear existing conditional formatting

    cell.FormatConditions.Delete

    ' Determine the color based on the cell's value

    If cell.Value < 14 Then

        ' Add orange data bar

        Set dataBar = cell.FormatConditions.AddDatabar

        dataBar.BarColor.Color = RGB(255, 192, 0) ' Orange

    ElseIf cell.Value >= 14 And cell.Value <18 Then

        ' Add yellow data bar

        Set dataBar = cell.FormatConditions.AddDatabar

        dataBar.BarColor.Color = RGB(255, 255, 0) ' Yellow

    Else

        ' Add green data bar

        Set dataBar = cell.FormatConditions.AddDatabar

        dataBar.BarColor.Color = RGB(0, 176, 80) ' Green

    End If

    ' Configure data bar settings

    With dataBar

        .MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0

        .MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=18

        .BarFillType = xlDataBarFillSolid

        .AxisPosition = xlDataBarAxisNone ' Hide the axis

    End With

Next cell

End Sub

Answer 2

Hi Snow,

Thanks for the reply. I need to share this workbook with others and unfortunately using Macros cannot solve the problem.

I will try to think of another way to solve it. Do you know if a Pivot Chart or other way could help me?

Answer 3

I guess there is no such built-in function can accomplish this task.