Apply Conditional Formatting in Worksheets

This article is designed to provide a detailed understanding of how to add conditional formatting to a range of cells in a worksheet.

Conditional formatting is an advanced feature in Microsoft Excel that allows you to apply formats to a range of cells, and have that formatting change depending on the value of the cell or the value of a formula. For example, the background of a cell may be red to highlight a negative value, or the text color might be green for a positive value. When the value of the cell meets the format condition, the format is applied. If the value of the cell does not meet the format condition, the cell’s default formatting is used.

It’s possible to apply conditional formatting with Microsoft Office Automation but that has its drawbacks. There are several reasons and issues involved: for example, security, stability, scalability and speed. The main reason for finding another solution is that Microsoft themselves strongly recommends against Office Automation for software solutions.

This article shows how to create a console application, add conditional formatting on cells with a few simplest lines of code using the Aspose.Cells API.

Using Aspose.Cells to Apply Conditional Formatting Based on Cell Value

1. Download and Install Aspose.Cells.
   1. Download Aspose.Cells for .NET.
2. Install it on your development computer. All Aspose components, when installed, work in evaluation mode. The evaluation mode has no time limit and it only injects watermarks into produced documents.
3. Create a project. Start Visual Studio.NET and create a new console application. This example creates a C# console application, but you can use VB.NET too.
4. Add references. Add a reference to Aspose.Cells to your project, for example add a reference to ….\Program Files\Aspose\Aspose.Cells\Bin\Net1.0\Aspose.Cells.dll
5. *Apply conditional formatting based on cell value. Below is the code used to accomplish the task. I applies conditional formatting on a cell.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Instantiating a Workbook object
Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

// Adds an empty conditional formatting
int index = sheet.ConditionalFormattings.Add();

FormatConditionCollection fcs = sheet.ConditionalFormattings[index];

// Sets the conditional format range.
CellArea ca = new CellArea();

ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;

fcs.AddArea(ca);

// Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "50", "100");

// Sets the background color.
FormatCondition fc = fcs[conditionIndex];

fc.Style.BackgroundColor = Color.Red;

// Saving the Excel file
workbook.Save(dataDir+ "output.out.xls", SaveFormat.Auto);

When the above code is executed, conditional formatting is applied to cell “A1” in first worksheet of the output file (output.xls). The conditional formatting applied to A1 depends on the cell value. If the cell value of A1 is between 50 and 100 the background color is red due to the conditional formatting applied.

Using Aspose.Cells to Apply Conditional Formatting Based on Formula

1. Applying conditional formatting depending on formula (Code Snippet) Below is the code to accomplish the task. It applies conditional formatting on B3.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Instantiating a Workbook object
Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

// Adds an empty conditional formatting
int index = sheet.ConditionalFormattings.Add();

FormatConditionCollection fcs = sheet.ConditionalFormattings[index];

// Sets the conditional format range.
CellArea ca = new CellArea();

ca = new CellArea();

ca.StartRow = 2;
ca.EndRow = 2;
ca.StartColumn = 1;
ca.EndColumn = 1;

fcs.AddArea(ca);


// Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.Expression);

// Sets the background color.
FormatCondition fc = fcs[conditionIndex];

fc.Formula1 = "=IF(SUM(B1:B2)>100,TRUE,FALSE)";

fc.Style.BackgroundColor = Color.Red;

sheet.Cells["B3"].Formula = "=SUM(B1:B2)";

sheet.Cells["C4"].PutValue("If Sum of B1:B2 is greater than 100, B3 will have RED background");

// Saving the Excel file
workbook.Save(dataDir+ "output.out.xls", SaveFormat.Auto);

When the above code is executed, conditional formatting is applied to cell “B3” in the first worksheet of the output file (output.xls). The conditional formatting applied depends on the formula which calculates the value of “B3” as sum of B1 & B2.