AddDatabar_ConditionalFormatting

Adds databars conditional formatting to range of cells.
Passing column, start row and end row as well as Sheet name and Workbooks as usual.
You can also decide the color.
Maybe next version, we can add more options to databar, like transparency.
If you need to not show value and show only bar, change .ShowValue = True to .ShowValue = False.

CodeFunctionName

What is this?

Public

Tested

Original Work

Function AddDatabar_ConditionalFormatting(ColumnAddress, StartRow, EndRow, RankColor, Optional Shee = "Active", Optional WB = "This")
    ' Will apply Databar conditional formatting to range ColumnAddress starting from StartRow to EndRow, having bar color as RankColor in RGB value
    '
    If WB = "This" Then WB = ThisWorkbook.Name
    If WB = "Active" Then WB = ActiveWorkbook.Name
    If Shee = "Active" Then Shee = ActiveSheet.Name
    Cell1 = ColumnAddress & StartRow
    Cell2 = ColumnAddress & EndRow
    Workbooks(WB).Worksheets(Shee).Range(Cell1, Cell2).FormatConditions.AddDatabar
    Workbooks(WB).Worksheets(Shee).Range(Cell1, Cell2).FormatConditions(Workbooks(WB).Worksheets(Shee).Range(Cell1, Cell2).FormatConditions.Count).ShowValue = True
    Workbooks(WB).Worksheets(Shee).Range(Cell1, Cell2).FormatConditions(Workbooks(WB).Worksheets(Shee).Range(Cell1, Cell2).FormatConditions.Count).SetFirstPriority
    With Workbooks(WB).Worksheets(Shee).Range(Cell1, Cell2).FormatConditions(1)
        .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
        .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
    End With
    With Workbooks(WB).Worksheets(Shee).Range(Cell1, Cell2).FormatConditions(1).BarColor
        .Color = RankColor '14928051
        .TintAndShade = 0
    End With
    Workbooks(WB).Worksheets(Shee).Range(Cell1, Cell2).FormatConditions(1).BarFillType = xlDataBarFillSolid
    Workbooks(WB).Worksheets(Shee).Range(Cell1, Cell2).FormatConditions(1).Direction = xlContext
    Workbooks(WB).Worksheets(Shee).Range(Cell1, Cell2).FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
    Workbooks(WB).Worksheets(Shee).Range(Cell1, Cell2).FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
    Workbooks(WB).Worksheets(Shee).Range(Cell1, Cell2).FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
    With Workbooks(WB).Worksheets(Shee).Range(Cell1, Cell2).FormatConditions(1).AxisColor
        .Color = 0
        .TintAndShade = 0
    End With
    With Workbooks(WB).Worksheets(Shee).Range(Cell1, Cell2).FormatConditions(1).NegativeBarFormat.Color
        .Color = 255
        .TintAndShade = 0
    End With
End Function

ColumnAddress, StartRow, EndRow, RankColor, Optional Shee = "Active", Optional WB = "This"