How to create a formula to apply conditional formatting in Excel in C#

We always use conditional formatting to highlight the cells with certain color from the whole data in the Excel worksheet. Spire.XLS also supports to create a formula to apply conditional formatting in Excel in C#. This article will show you how to apply a conditional formatting rule.

View the steps of Microsoft Excel set the conditional formatting.

Step 1: Choose the column B and then click "New Rule" under "Conditional Formatting"

Step 2: Select the rule type, enter the rule by adding the formula and then add the highlight color for the format.

How to create a formula to apply conditional formatting in Excel in C#

Here comes to the steps of how to set the conditional formatting rule by Spire.XLS in C#.

Step 1: Create a new excel workbook and load the document from file.

Workbook wb = new Workbook();
wb.LoadFromFile("Test.xlsx");

Step 2: Get the first worksheet and the second column from the workbook.

Worksheet sheet = wb.Worksheets[0];
CellRange range = sheet.Columns[1];

Step 3: Set the conditional formatting formula and apply the rule to the chosen cell range.

XlsConditionalFormats xcfs = sheet.ConditionalFormats.Add();
xcfs.AddRange(range);
IConditionalFormat conditional = xcfs.AddCondition();
conditional.FormatType = ConditionalFormatType.Formula;
conditional.FirstFormula = "=($B1<$C1)";
conditional.BackKnownColor = ExcelColors.Yellow;

Step 4: Save the document to file.

wb.SaveToFile("result.xlsx", ExcelVersion.Version2010);

Effective screenshot:

How to create a formula to apply conditional formatting in Excel in C#

Full codes:

using Spire.Xls;

namespace  CreateFormula
{
    class Program
    {
        static void Main(string[] args)
        {
                Workbook wb = new Workbook();
                wb.LoadFromFile("Test.xlsx");

                Worksheet sheet = wb.Worksheets[0];
                CellRange range = sheet.Columns[1];

                XlsConditionalFormats xcfs = sheet.ConditionalFormats.Add();
                xcfs.AddRange(range);
                IConditionalFormat conditional = xcfs.AddCondition();
                conditional.FormatType = ConditionalFormatType.Formula;
                conditional.FirstFormula = "=($B1<$C1)";
                conditional.BackKnownColor = ExcelColors.Yellow;

                wb.SaveToFile("result.xlsx", ExcelVersion.Version2010);
        }
    }
}

VB.NET

Imports Spire.Xls

Namespace CreateFormula
  Class Program
    Private Shared Sub Main(args As String())
      Dim wb As New Workbook()
      wb.LoadFromFile("Test.xlsx")

      Dim sheet As Worksheet = wb.Worksheets(0)
      Dim range As CellRange = sheet.Columns(1)

      Dim xcfs As XlsConditionalFormats = sheet.ConditionalFormats.Add()
      xcfs.AddRange(range)
      Dim conditional As IConditionalFormat = xcfs.AddCondition()
      conditional.FormatType = ConditionalFormatType.Formula
      conditional.FirstFormula = "=($B1<$C1)"
      conditional.BackKnownColor = ExcelColors.Yellow

      wb.SaveToFile("result.xlsx", ExcelVersion.Version2010)
    End Sub
  End Class
End Namespace

How to create a formula to apply conditional formatting in Excel in C#

See Also