Apply Conditional Formatting to Cells of Excel in C#.NET
  1. Home
  2. Excel Editing in .NET
  3. Adding Conditional Formatting to Spreadsheet Data Range C# Tutorial
Error processing SSI file

How to Apply Conditional Formatting to Excel in C#.NET Code

iDiTect.Excel provides adding conditional formatting to a cell or range of cells, based on the cell data value or the formula value.

How to Insert Conditional Formatting to Spreadsheet in C# language

Load or Import Data

In this C# tutorial, we create some sample data to spreadsheet columns.

for (int col = 1; col < 10; col++)
{
    worksheet.Cell(1, col).Value = "Sample " + col;

    for (int row = 2; row < 12; row++)
    {
        worksheet.Cell(row, col).Value = row - 1;
    }
}

worksheet.Cell(1, 10).Value = "Sample 10";
for (int row = 2; row < 12; row++)
{
    worksheet.Cell(row, 10).Value = DateTime.Now.AddDays(-row + 1);
    worksheet.Cell(row,10).Style.NumberFormat.Format = "mm-dd-yy";
}

Add Rule for Color Scale

First define a Range navigate to cell "A2:A11". LowestValue()/HighestValue() for two color scale, and LowestValue()/Midpoint()/HighestValue() for three color scale 

var range = worksheet.Range("A2:A11");
var rule1 = range.AddConditionalFormat().ColorScale();
rule1.LowestValue(ExcelColor.Red);
//You can set 2 or 3 color scale, if want to show 3 color scale, modify the "Midpoint"
//rule1.Midpoint(ConditionalFormatContentType.Percent, 50, ExcelColor.Pear);
rule1.HighestValue(ExcelColor.Purple);

Add Rule for IsTop or like

  • IsTop()
  • IsBottom()

Make a Range point to cell "B2:B11". To find target items, or target percentage items are both available. Here set to find top 3 items, it will highlight the cells whose values matched.

range = worksheet.Range(2, 2, 11, 2); //="B2:B11"
//find top 3 items, depend on the bigger value
var rule2 = range.AddConditionalFormat().IsTop(3);
//or find top 30% items, depend on the bigger value
//var rule2 = range.AddConditionalFormat().IsTop(30, TopBottomType.Percent);
rule2.Font.FontColor = ExcelColor.Red;

Add Rule for IsStartsWith or like

  • IsStartsWith()
  • IsEndsWith()
  • IsContain()
  • IsNotContain()

Set a Range to cell "C2:C11". Set the Text of rule to "3". So it will highlight the cells whose text values start with "3".

range = worksheet.Range("C2:C11");
var rule3 = range.AddConditionalFormat().IsStartsWith("3");
rule3.Fill.BackgroundColor = ExcelColor.Red;

Add Rule for IsBetween or like

  • IsBetween()
  • IsNotBetween()

Point a Range to cell "D2:D11". Set the min value to 4, max value to 6. So it will highlight the cells whose values between 4 and 6.

range = worksheet.Range("D2:D11");
var rule4 = range.AddConditionalFormat().IsBetween(4, 6);
rule4.Font.FontColor = ExcelColor.Red;

Add Rule for IsGreaterThan or like

  • IsGreaterThan()
  • IsGreaterThanOrEqual()
  • IsLessThan()
  • IsLessThanOrEqual()
  • IsEqual()
  • IsNotEqual()

Navigate a Range to cell "E2:E11". Set the rule to "5". So it will highlight the cells whose values greater than 5.

range = worksheet.Range("E2:E11");
var rule5 = range.AddConditionalFormat().IsGreaterThan(5);
rule5.Font.FontColor = ExcelColor.Red;

Add Rule for IsBlank or like

  • IsBlank()
  • IsNotBlank()
  • IsError()
  • IsNotError()
  • IsDuplicate()
  • IsUnique()

Navigate a Range to cell "F2:F11". This rule will check the select cell automatically, and highlight the available ones.

range = worksheet.Range("F2:F11");
var rule6 = range.AddConditionalFormat().IsBlank();
rule6.Font.FontColor = ExcelColor.Red;

Add Rule for IsTrue

Navigate a Range to cell "G2:G11". Set the rule to "IF(G2-G3<=0,1,0)", or directly judge "Ture" or "False".

range = worksheet.Range("G2:G11");
var rule7 = range.AddConditionalFormat().IsTrue("IF(G2-G3<=0,1,0)");
//or just simply set the rule to TRUE
//var rule7 = range.AddConditionalFormat().IsTrue("TRUE");
rule7.Font.FontColor = ExcelColor.Red;

Add Rule for DataBar

Navigate a Range to cell "H2:H11". Show backgourd as bar graph in cell depending on the cell data value.

range = worksheet.Range("H2:H11");
var rule8 = range.AddConditionalFormat().DataBar(ExcelColor.Red);
//Set all data value to show bar with gradient
rule8.LowestValue();
rule8.HighestValue();
//or set target data value to show bar with gradient
//rule8.Minimum(ConditionalFormatContentType.Number, 3);
//rule8.Maximum(ConditionalFormatContentType.Percent, 80);

Add Rule for Icon Set

Navigate a Range to cell "I2:I11". Show embedded icons at the beginning of the cell.

range = worksheet.Range("I2:I11");
var rule9 = range.AddConditionalFormat().IconSet(IconSetStyle.ThreeArrows);
rule9.AddValue(ConditionalFormatIconSetOperator.GreaterThanOrEqual, "3", ConditionalFormatContentType.Number);
rule9.AddValue(ConditionalFormatIconSetOperator.GreaterThan, "5", ConditionalFormatContentType.Number);
rule9.AddValue(ConditionalFormatIconSetOperator.GreaterThan, "7", ConditionalFormatContentType.Number);

Add Rule for DateIn

  • DateIn(TimePeriod.Last7Days)
  • DateIn(TimePeriod.Today)
  • DateIn(TimePeriod.Yesterday)
  • DateIn(TimePeriod.Tomorrow)
  • DateIn(TimePeriod.ThisWeek)
  • DateIn(TimePeriod.LastWeek)
  • DateIn(TimePeriod.NextWeek)
  • DateIn(TimePeriod.ThisMonth)
  • DateIn(TimePeriod.LastMonth)
  • DateIn(TimePeriod.NextMonth)

Navigate a Range to cell "J2:J11". Set TimePeriod to Last7Days, it will highlight the available ones.

range = worksheet.Range("J2:J11");
var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.Last7Days);
rule10.Font.FontColor = ExcelColor.Red;

Full code for adding Excel conditional formatting in C#

var workbook = new Workbook();
var worksheet = workbook.Worksheets.Add("ConditionalFormatting");

// Create 9 columns of samples data
for (int col = 1; col < 10; col++)
{
    worksheet.Cell(1, col).Value = "Sample " + col;

    for (int row = 2; row < 12; row++)
    {
        worksheet.Cell(row, col).Value = row - 1;
    }
}

var range = worksheet.Range("A2:A11");
var rule1 = range.AddConditionalFormat().ColorScale();
rule1.LowestValue(ExcelColor.Red);
//You can set 2 or 3 color scale, if want to show 3 color scale, modify the "Midpoint"
//rule1.Midpoint(ConditionalFormatContentType.Percent, 50, ExcelColor.Pear);
rule1.HighestValue(ExcelColor.Purple);


range = worksheet.Range(2, 2, 11, 2); //="B2:B11"
//find top 3 items, depend on the bigger value
var rule2 = range.AddConditionalFormat().IsTop(3);
//or find top 30% items, depend on the bigger value
//var rule2 = range.AddConditionalFormat().IsTop(30, TopBottomType.Percent);
rule2.Font.FontColor = ExcelColor.Red;
//similar
//var rule2 = range.AddConditionalFormat().IsBottom(3);

range = worksheet.Range("C2:C11");
var rule3 = range.AddConditionalFormat().IsStartsWith("3");
rule3.Fill.BackgroundColor = ExcelColor.Red;
//similar
//var rule3 = range.AddConditionalFormat().IsEndsWith("3");
//var rule3 = range.AddConditionalFormat().IsContain("3");
//var rule3 = range.AddConditionalFormat().IsNotContain("3");

range = worksheet.Range("D2:D11");
var rule4 = range.AddConditionalFormat().IsBetween(4, 6);
rule4.Font.FontColor = ExcelColor.Red;
//similar
//var rule4 = range.AddConditionalFormat().IsNotBetween();

range = worksheet.Range("E2:E11");
var rule5 = range.AddConditionalFormat().IsGreaterThan(5);
rule5.Font.FontColor = ExcelColor.Red;
//similar
//var rule5 = range.AddConditionalFormat().IsGreaterThanOrEqual(5);
//var rule5 = range.AddConditionalFormat().IsLessThan(5);
//var rule5 = range.AddConditionalFormat().IsLessThanOrEqual(5);
//var rule5 = range.AddConditionalFormat().IsEqual(5);
//var rule5 = range.AddConditionalFormat().IsNotEqual(5);

range = worksheet.Range("F2:F11");
var rule6 = range.AddConditionalFormat().IsBlank();
rule6.Font.FontColor = ExcelColor.Red;
//similar
//var rule6 = range.AddConditionalFormat().IsNotBlank();
//var rule6 = range.AddConditionalFormat().IsError();
//var rule6 = range.AddConditionalFormat().IsNotError();
//var rule6 = range.AddConditionalFormat().IsDuplicate();
//var rule6 = range.AddConditionalFormat().IsUnique();

range = worksheet.Range("G2:G11");
var rule7 = range.AddConditionalFormat().IsTrue("IF(G2-G3<=0,1,0)");
//or just simply set the rule to TRUE
//var rule7 = range.AddConditionalFormat().IsTrue("TRUE");
rule7.Font.FontColor = ExcelColor.Red;

range = worksheet.Range("H2:H11");
var rule8 = range.AddConditionalFormat().DataBar(ExcelColor.Red);
//Set all data value to show bar with gradient
rule8.LowestValue();
rule8.HighestValue();
//or set target data value to show bar with gradient
//rule8.Minimum(ConditionalFormatContentType.Number, 3);
//rule8.Maximum(ConditionalFormatContentType.Percent, 80);

range = worksheet.Range("I2:I11");
var rule9 = range.AddConditionalFormat().IconSet(IconSetStyle.ThreeArrows);
rule9.AddValue(ConditionalFormatIconSetOperator.GreaterThanOrEqual, "3", ConditionalFormatContentType.Number);
rule9.AddValue(ConditionalFormatIconSetOperator.GreaterThan, "5", ConditionalFormatContentType.Number);
rule9.AddValue(ConditionalFormatIconSetOperator.GreaterThan, "7", ConditionalFormatContentType.Number);


worksheet.Cell(1, 10).Value = "Sample 10";
for (int row = 2; row < 12; row++)
{
    worksheet.Cell(row, 10).Value = DateTime.Now.AddDays(-row + 1);
    worksheet.Cell(row,10).Style.NumberFormat.Format = "mm-dd-yy";
}

range = worksheet.Range("J2:J11");
var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.Last7Days);
rule10.Font.FontColor = ExcelColor.Red;
//similar
//var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.Today);
//var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.Yesterday);
//var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.Tomorrow);
//var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.ThisWeek);
//var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.LastWeek);
//var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.NextWeek);
//var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.ThisMonth);
//var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.LastMonth);
//var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.NextMonth);

workbook.Save("ConditionalFormatting.xlsx");
What iDiTect .NET Document component can do

We provide powerful & profession document & image controls: How search particular string in excel sheet The Find method of the Range object enables you to search for text in Excel Xlsx spreadsheet. C# read Excel and Obtain Range automatically how to obtain a reference to a range of Excel cells for manipulation and then do so (operate on the range) Create Edit Convert Process PDF Files in C# ASP.NET you can use a PDF library, load your PDF form, set the values to each field, flatten your file C# PDF insert image Library learn how to insert an image into PDF file using iDiTect.PDF in asp.net with C#. How to Calculate Formulas in XLSX Document in C# Spreadsheet SDK provides Excel calculation engine for your desktop and web applications without Excel installed. How to insert a picture in to Excel from C# app insert image into .xlsx spreadsheet in C# language.

More Programming Tutorials
More Programming FAQs

We offer full featured SDK for 30 days evaluation test.

Download Free     Order Now