Excel VBA - Apply Conditional Formatting Data Bars with VBA Macro

To add Data Bars with conditional formatting use the AddDatabar method of the FormatConditions object. You can then use properties to format bar color, fill type (gradient or solid) and border. If you have negative values you can add an axis and if you like apply different formatting to the negative bars.

Sub databars()
Dim rg As Range
Dim db As Databar
Set rg = Range("T6", Range("T6").End(xlDown))
Set db = rg.FormatConditions.AddDatabar

With db
    'positive bar formatted with black gradient & black border
    .BarColor.Color = vbBlack
    .BarFillType = xlDataBarFillGradient
    .BarBorder.Type = xlDataBarBorderSolid
    .BarBorder.Color.Color = vbBlack
    'the axis positioned automatically and coloured red
    .AxisPosition = xlDataBarAxisAutomatic
    .AxisColor.Color = vbRed
    'the negative bar formatted with a red gradient and red border
    With .NegativeBarFormat
        .ColorType = xlDataBarColor
        .Color.Color = vbRed
        .BorderColorType = xlDataBarColor
        .BorderColor.Color = vbRed
    End With
End With

End Sub

The code above relates to the data shown below.

Excel VBA – Apply Conditional Formatting Data Bars with VBA Macro