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"

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

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.

ConditionalFormatWrapper conditional = range.ConditionalFormats.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:

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

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

    ConditionalFormatWrapper conditional = range.ConditionalFormats.AddCondition();
    conditional.FormatType = ConditionalFormatType.Formula;
    conditional.FirstFormula = "=($B1<$C1)";
    conditional.BackKnownColor = ExcelColors.Yellow;

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