Contitional Formating, Data Bars

[GHWels]

Hi,

Please can you help me to format the Databar like my sample,

Dim conditional As XlsConditionalFormats = sheet.ConditionalFormats.Add()
conditional.AddRange(sheet.Range("B1:B4"))
Dim format As IConditionalFormat = conditional.AddCondition()
format.FormatType = ConditionalFormatType.DataBar
..?

Thank you
GH

[Brian.Li]

Hello,

Thanks for your inquiry and sorry for the late reply as weekend.
Please refer to the following code. If there are any questions, please feel free to contact us.

Code: Select all

        Dim workbook As Workbook = New Workbook
        Dim sheet As Worksheet = workbook.Worksheets(0)
        Dim conditional As XlsConditionalFormats = sheet.ConditionalFormats.Add()
        sheet.Range("B1").NumberValue = -0.2
        sheet.Range("B2").NumberValue = -0.3
        sheet.Range("B3").NumberValue = 0.12
        sheet.Range("B4").NumberValue = 1
        sheet.Range("B1:B4").NumberFormat = "0%"
        conditional.AddRange(sheet.Range("B1:B4"))
        Dim format As IConditionalFormat = conditional.AddCondition()
        format.FormatType = ConditionalFormatType.DataBar
        format.DataBar.AxisPosition = DataBarAxisPosition.DataBarAxisMidpoint
        format.DataBar.BarFillType = DataBarFillType.DataBarFillGradient
        Dim cellRanges As CellRange = sheet.Range("B1:B4")
        For Each cellrange As CellRange In cellRanges
            If (cellrange.NumberValue > 0) Then
                format.DataBar.BarColor = Color.CadetBlue
            Else
                format.DataBar.BarColor = Color.IndianRed
            End If
        Next
        workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013)
        System.Diagnostics.Process.Start("result.xlsx")

Sincerely,
Brian
E-iceblue support team

[GHWels]

Hey Brian,

Thank you very much for your fast and perfect answer.

GH

[Brian.Li]

You are welcome. If you have any other question, please feel free to contact us.

Sincerely,
Brian
E-iceblue support team