officefileapi-116956-excel-export-library-conditional-formatting-how-to-format-blank-cells.md
The following example demonstrates how to specify the rule that highlights the blank cells.
Create new XlConditionalFormatting instance that contains formatting rules and settings.
Specify the range to which the formatting is going to be applied by adding it to the ranges collection, accessible through the XlConditionalFormatting.Ranges property.
Create new XlCondFmtRuleBlanks object, representing the new formatting rule.
Specify the formatting parameters to the cells, conforming to the condition.
Add the newly created rule to the collection of rules contained in the XlConditionalFormatting object. To do that, use the Add method.
To activate the created conditional formatting rule, add the object created in step 1 to the worksheet collection of conditional formatting rules. The collection can be accessed through the IXlSheet.ConditionalFormattings property.
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A1:A10).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 9));
// Create the rule to highlight blank cells in the range.
XlCondFmtRuleBlanks rule = new XlCondFmtRuleBlanks(true);
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Create the rule to highlight non-blank cells in the range.
rule = new XlCondFmtRuleBlanks(false);
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Good;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
' Create an instance of the XlConditionalFormatting class.
Dim formatting As New XlConditionalFormatting()
' Specify the cell range to which the conditional formatting rules should be applied (A1:A10).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 9))
' Create the rule to highlight blank cells in the range.
Dim rule As New XlCondFmtRuleBlanks(True)
' Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad
formatting.Rules.Add(rule)
' Create the rule to highlight non-blank cells in the range.
rule = New XlCondFmtRuleBlanks(False)
' Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Good
formatting.Rules.Add(rule)
' Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting)
The image below illustrates formatted worksheet. The blank cells are formatted using the “Bad” cell style, the non-empty cells are formatted using the “Good” cell style.