officefileapi-116951-excel-export-library-conditional-formatting-how-to-format-cells-that-are-less-than-greater-than-or-between-a-value.md
This example demonstrates how to create the rule that uses a relational operator as a formatting criterion.
Create new XlConditionalFormatting instance that contains formatting rules and settings.
Specify the range to which the formatting is going to be applied. To do that, add the target range to the object’s ranges collection, accessible through the XlConditionalFormatting.Ranges property.
Create new XlCondFmtRuleCellIs object, representing the new formatting rule.
Set the object’s XlCondFmtRuleCellIs.Operator property to the corresponding XlCondFmtOperator enumeration value to specify the formatting condition.
To specify the threshold value, use the XlCondFmtRuleCellIs.Value property. Note that the value can be represented by a formula.
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:A11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10));
// Create the rule to highlight cells whose values are less than 5.
XlCondFmtRuleCellIs rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.LessThan;
rule.Value = 5;
// 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 cells whose values are between 5 and 8.
rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.Between;
rule.Value = 5;
rule.SecondValue = 8;
// Specify formatting settings to be applied to cells
// if the condition is true.
rule.Formatting = XlCellFormatting.Neutral;
formatting.Rules.Add(rule);
// Create the rule to highlight cells whose values are greater than 8.
rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.GreaterThan;
rule.Value = 8;
// 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.
formatting = new XlConditionalFormatting();
// Specify the cell range
// to which the conditional formatting rule should be applied (B1:B11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10));
// Create the rule to highlight cells
// whose values are greater than a value calculated by a formula.
rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.GreaterThan;
rule.Value = "=$A1+3";
// Specify formatting settings to be applied to cells
// if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
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:A11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10))
' Create the rule to highlight cells whose values are less than 5.
Dim rule As New XlCondFmtRuleCellIs()
rule.Operator = XlCondFmtOperator.LessThan
rule.Value = 5
' 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 cells whose values are between 5 and 8.
rule = New XlCondFmtRuleCellIs()
rule.Operator = XlCondFmtOperator.Between
rule.Value = 5
rule.SecondValue = 8
' Specify formatting settings to be applied to cells
' if the condition is true.
rule.Formatting = XlCellFormatting.Neutral
formatting.Rules.Add(rule)
' Create the rule to highlight cells whose values are greater than 8.
rule = New XlCondFmtRuleCellIs()
rule.Operator = XlCondFmtOperator.GreaterThan
rule.Value = 8
' 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.
formatting = New XlConditionalFormatting()
' Specify the cell range
' to which the conditional formatting rule should be applied (B1:B11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10))
' Create the rule to highlight cells whose values are greater than a value calculated by a formula.
rule = New XlCondFmtRuleCellIs()
rule.Operator = XlCondFmtOperator.GreaterThan
rule.Value = "=$A1+3"
' Specify formatting settings to be applied to cells
' if the condition is true.
rule.Formatting = XlCellFormatting.Bad
formatting.Rules.Add(rule)
' Add the specified format options
' to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting)
The image below illustrates the result of code execution.