Nsryjdtyk

I have a column in Excel containing positive and negative numbers and I make a data bar. I would like if lets say I have 55, 24, 0, -10 and -45 the 55 to be filled completely with green bar maximum span and -45 the same but red. This is the VBA code that I have so far:

This is visualisation wath I want to achieve:

DataBar

|||||||||||| (green) 12

|||||| (green) 6

|||||||||| (red) -10

|||||||||||||| (red) -14

and so on.....

Function UpdateAmountBars(rng As Range)

    Dim min As Double, max As Double

    Let min = Application.min(rng)

    Let max = Application.max(rng)



    rng.FormatConditions.AddDatabar

    rng.FormatConditions(rng.FormatConditions.Count).ShowValue = True

    rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority

    With rng.FormatConditions(1)

            .MinPoint.Modify newtype:=xlConditionValueAutomaticMin

            .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax

    End With



    With rng.FormatConditions(1).BarColor

        .Color = RGB(100, 255, 100)

        .TintAndShade = 0

    End With



    rng.FormatConditions(1).BarFillType = xlDataBarFillGradient

    rng.FormatConditions(1).Direction = xlContext

    rng.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor

    rng.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone



    With rng.FormatConditions(1).NegativeBarFormat.Color

        .Color = RGB(255, 100, 100)

        .TintAndShade = 0

    End With



End Function

and this is the result of the code:
VBA DataBAR positive negative

Update: I have some progress, now the databars look good but still I don't want the cells to be divided

Function UpdateAmountBars(rng As Range)

    Dim min As Double, max As Double, databar As databar

    Let min = Application.min(rng)

    Let max = Application.max(rng)



    Set databar = rng.FormatConditions.AddDatabar

    databar.AxisPosition = xlDataBarAxisAutomatic

    rng.FormatConditions(rng.FormatConditions.Count).ShowValue = True

    rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority

    With rng.FormatConditions(1)

            .MinPoint.Modify newtype:=xlConditionValueAutomaticMin, newvalue:=min

            .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax, newvalue:=max

    End With



    With rng.FormatConditions(1).BarColor

        .Color = RGB(100, 255, 100)

        .TintAndShade = 0

    End With



    rng.FormatConditions(1).BarFillType = xlDataBarFillGradient

    rng.FormatConditions(1).Direction = xlContext

    rng.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor

    rng.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone



    With rng.FormatConditions(1).NegativeBarFormat.Color

        .Color = RGB(255, 100, 100)

        .TintAndShade = 0

    End With

End Function

this is the update result: VBA Data bar axisPosition

Add and try following code.

.FormatConditions(1).AxisPosition = None

Use the above code somewhere here in your code.

rng.FormatConditions(1).BarFillType = xlDataBarFillGradient

rng.FormatConditions(1).Direction = xlContext

rng.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor

rng.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone

I'm having the same issue with my data bars. The bars are largest for negative values closest to 0 and the bars become smaller the larger the negative number is, which is the opposite of what I want to achieve.