You can set up conditional formats within cells that determine the formatting of the cell based on the outcome of a conditional statement. You can specify various formatting options such as borders and colors to apply if the condition statement is valid, that is, if the operation is satisfied.

For example, you may want to change the background color of a cell based on the value of the cell. If the value is below 100 then the background color would be changed to red. The condition statement is "less than 100" and consists of a comparison operator "less than" and a condition, in this case a single constant "100". The condition can be a constant (expressed as a string) or an expression. Some condition statements have two conditions and an operator: for instance, if the cell value is between 0 and 100, then change the background color. In this case, the comparison operator is "between" and the first condition is 0 and the last condition is 100. For more information about the possible style settings, refer to Creating and Applying a Style for Cells.

If two conditional formats are set to the same cell, the second conditional format takes effect.

The conditional formatting can be done using the ConditionalFormatting class and any of the following members of the SheetView class:

• GetConditionalFormatting method
• SetConditionalFormatting method
• ClearConditionalFormating method

When you use the GetConditionalFormatting method, the conditions, operator, and style information are returned as a ConditionalFormatting object. The first condition can be either a string or expression (FirstCondition or FirstConditionExpression.) Similarly, the last condition can be a string or expression (LastCondition or LastConditionExpression). If only one condition is set, it is in the FirstCondition and the LastCondition is null. The ComparisonOperator is the comparison operator for the conditional format. The style settings to apply to the cell when the condition statement is true are set as an object.

Refer to the following code examples to see how to set conditional formatting for a range of cells that would result in different background colors, for instance, as shown in the following figure.

For some cell types that allow input of multiple data types, such as general cell type, conditional formatting works whether you type in numbers or strings. For example, if you have conditional formatting set for values between various ranges such as 10 to 20 and 20 to 30, then typing 16 results in the formatting for the range 10 to 20. If you then type 16m, the cell treats this like a string and since "16m" is between strings "10" and "20", the conditional formatting still applies.

Use the ClearConditionalFormatting method to clear only the conditional formats of a cell without affecting the other formatting or the contents of the cell.

Using Code

1. Define formatting rule.
2. Set conditional formatting for a range of cells.

Example

This example code defines the conditional formatting rule for a range of cells and changes the coloring of the cells based on the specified rule. To see how it works, type a number in cell B2, then either change cells or leave edit mode to see if the formatting is applied.

FarPoint.Win.Spread.UnaryComparisonConditionalFormattingRule unary = new FarPoint.Win.Spread.UnaryComparisonConditionalFormattingRule(FarPoint.Win.Spread.UnaryComparisonOperator.EqualTo, 10, false);
unary.Operator = FarPoint.Win.Spread.UnaryComparisonOperator.EqualTo;
unary.Value = 10;
unary.BackColor = Color.Red;
unary.FontStyle = new FarPoint.Win.Spread.SpreadFontStyle(FarPoint.Win.Spread.RegularBoldItalicFontStyle.Bold);
FarPoint.Win.Spread.BetweenValuesConditionalFormattingRule between = new FarPoint.Win.Spread.BetweenValuesConditionalFormattingRule(true, 10, false, 20, false);
between.FirstValue = 1;
between.SecondValue = 20;
between.IsNotBetween = true;
between.BackColor = Color.Bisque;
// Overloading the SetConditionalFormatting Method

fpSpread1.ActiveSheet.SetConditionalFormatting(0, 0, 3, 3, false, unary, between);
   

Dim unary As FarPoint.Win.Spread.UnaryComparisonConditionalFormattingRule = New FarPoint.Win.Spread.UnaryComparisonConditionalFormattingRule(FarPoint.Win.Spread.UnaryComparisonOperator.EqualTo, 10, false)
unary.Operator = FarPoint.Win.Spread.UnaryComparisonOperator.EqualTo
unary.Value = 10
unary.BackColor = Color.Red
unary.FontStyle = New FarPoint.Win.Spread.SpreadFontStyle(FarPoint.Win.Spread.RegularBoldItalicFontStyle.Bold)
Dim between As FarPoint.Win.Spread.BetweenValuesConditionalFormattingRule = New FarPoint.Win.Spread.BetweenValuesConditionalFormattingRule(true, 10, false, 20, false)
between.FirstValue = 1
between.SecondValue = 20
between.IsNotBetween = true
between.BackColor = Color.Bisque
' Overloading the SetConditionalFormatting Method
fpSpread1.ActiveSheet.SetConditionalFormatting(0, 0, 3, 3, false, unary, between)