XL.ConditionalFormating.AddOpNumRule
------------------------------------

Adds a conditional formatting rule that highlights cells whose values are compared with a calculated result, using an operator.

| Component | Version | macOS | Windows | Linux | Server | iOS SDK |
|---|---|---|---|---|---|---|
| [XL](component_XL.md) | [14.2](newinversion142.md) | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |

MBS( "XL.ConditionalFormating.AddOpNumRule"; BookRef; ConditionalFormattingRef; Operator; ConditionalFormatRef; Value1 { ; Value2; StopIfTrue } ) 

**MBS**( **"XL.ConditionalFormating.AddOpNumRule";** /\* Adds a conditional formatting rule that highlights cells whose values are compared with a calculated result, using an operator. \*/  
**$BookRef**; /\* The reference number for the workbook.e.g. $ref \*/   
**$ConditionalFormattingRef**; /\* The reference number for the conditional formatting.e.g. $ConditionalFormatting \*/   
**$Operator**; /\* The operator in the conditional formatting rule, see the list of operators above.e.g. 2 \*/   
**$ConditionalFormatRef**; /\* The reference number for the conditional format.  
The conditional format used for highlighting cells, use the [XL.Book.AddConditionalFormat](XLBookAddConditionalFormat.md) for adding conditional formats.e.g. $ConditionalFormat \*/   
**$Value1**; /\* The numeric value for the expression with the specified operator. \*/   
**$Value2**; /\* Optional; The numeric second optional value for the expression only for some operators. \*/   
**$StopIfTrue**) /\* Optional; If true, no rules with lower priority may be applied over this rule, when this rule is true.  
Default is false. \*/ 

### Parameters

| Parameter | Description | Example | Flags |
|---|---|---|---|
| BookRef | The reference number for the workbook. | $ref |  |
| ConditionalFormattingRef | The reference number for the conditional formatting. | $ConditionalFormatting |  |
| Operator | The operator in the conditional formatting rule, see the list of operators above. | 2 |  |
| ConditionalFormatRef | The reference number for the conditional format.   The conditional format used for highlighting cells, use the [XL.Book.AddConditionalFormat](XLBookAddConditionalFormat.md) for adding conditional formats. | $ConditionalFormat |  |
| Value1 | The numeric value for the expression with the specified operator. |  |  |
| Value2 | The numeric second optional value for the expression only for some operators. |  | Optional |
| StopIfTrue | If true, no rules with lower priority may be applied over this rule, when this rule is true.   Default is false. |  | Optional |

### Result

Returns OK or error.

### Description

Adds a conditional formatting rule that highlights cells whose values are compared with a calculated result, using an operator.  
| Name | Value | Description |
|---|---|---|
| CFOPERATOR\_LESSTHAN | 0 | Less than' operator |
| CFOPERATOR\_LESSTHANOREQUAL | 1 | Less than or equal to' operator |
| CFOPERATOR\_EQUAL | 2 | Equal to' operator |
| CFOPERATOR\_NOTEQUAL | 3 | Not equal to' operator |
| CFOPERATOR\_GREATERTHANOREQUAL | 4 | Greater than or equal to' operator |
| CFOPERATOR\_GREATERTHAN | 5 | Greater than' operator |
| CFOPERATOR\_BETWEEN | 6 | Between' operator |
| CFOPERATOR\_NOTBETWEEN | 7 | Not between' operator |
| CFOPERATOR\_CONTAINSTEXT | 8 | Contains' operator |
| CFOPERATOR\_NOTCONTAINS | 9 | Does not contain' operator |
| CFOPERATOR\_BEGINSWITH | 10 | Begins with' operator |
| CFOPERATOR\_ENDSWITH | 11 | Ends with' operator |

### Examples

Highlighting cells that more than the specified value:

 # add conditional format   
Set Variable \[ $cFormat ; Value: MBS ("[XL.Book.AddConditionalFormat](XLBookAddConditionalFormat.md)"; $book ) \]  
Set Variable \[ $r ; Value: MBS ("[XL.ConditionalFormat.SetFillPattern](XLConditionalFormatSetFillPattern.md)"; $book ; $cFormat ; 1 /\* solid \*/) \]  
Set Variable \[ $r ; Value: MBS ("[XL.ConditionalFormat.SetPatternBackgroundColor](XLConditionalFormatSetPatternBackgroundColor.md)"; $book ; $cFormat ; 42 /\* light green \*/) \]  
\#   
\# add conditional format to color cells based on value   
Set Variable \[ $[CF](CF.md) ; Value: MBS ("[XL.Sheet.AddConditionalFormatting](XLSheetAddConditionalFormatting.md)"; $book ; $sheet ) \]  
Set Variable \[ $r ; Value: MBS ("[XL.ConditionalFormating.AddRange](XLConditionalFormatingAddRange.md)"; $book ; $[CF](CF.md); 3; 10; 2; 2) \]  
Set Variable \[ $r ; Value: MBS ("XL.ConditionalFormating.AddOpNumRule"; $book ; $[CF](CF.md); 5 /\* GreaterThan \*/; $cFormat ; 90) \]  
### See also

- [XL.Book.AddConditionalFormat](XLBookAddConditionalFormat.md)
- [XL.ConditionalFormat.SetFillPattern](XLConditionalFormatSetFillPattern.md)
- [XL.ConditionalFormat.SetPatternBackgroundColor](XLConditionalFormatSetPatternBackgroundColor.md)
- [XL.ConditionalFormating.AddOpStrRule](XLConditionalFormatingAddOpStrRule.md)
- [XL.ConditionalFormating.AddRange](XLConditionalFormatingAddRange.md)
- [XL.ConditionalFormating.AddRule](XLConditionalFormatingAddRule.md)
- [XL.ConditionalFormating.AddTimePeriodRule](XLConditionalFormatingAddTimePeriodRule.md)
- [XL.ConditionalFormating.AddTopRule](XLConditionalFormatingAddTopRule.md)
- [XL.Sheet.AddConditionalFormatting](XLSheetAddConditionalFormatting.md)

### Example Databases

- [XL/Conditional Formatting](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/XL/Conditional%20Formatting.shtml#3ScriptAnchor_)

### Blog Entries

- [Conditional Formatting in Excel exports from FileMaker](https://www.mbsplugins.de/archive/2024-04-08/Conditional_Formatting_in_Exce/monkeybreadsoftware_blog_filemaker)

Created 30th March 2024 , last changed 31st March 2024

  
[XL.ConditionalFormating.AddAboveAverageRule](XLConditionalFormatingAddAboveAverageRule.md) - [XL.ConditionalFormating.AddOpStrRule](XLConditionalFormatingAddOpStrRule.md)

[HTML Version](XLConditionalFormatingAddOpNumRule.shtml)