XL.ConditionalFormating.AddRule
-------------------------------

Adds a conditional formatting rule that highlights cells whose values correspond to the specified criteria.

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

MBS( "XL.ConditionalFormating.AddRule"; BookRef; ConditionalFormattingRef; FormatType; ConditionalFormatRef { ; Value; StopIfTrue } ) 

**MBS**( **"XL.ConditionalFormating.AddRule";** /\* Adds a conditional formatting rule that highlights cells whose values correspond to the specified criteria. \*/  
**$BookRef**; /\* The reference number for the workbook.e.g. $ref \*/   
**$ConditionalFormattingRef**; /\* The reference number for the conditional formatting.e.g. $ConditionalFormatting \*/   
**$FormatType**; /\* The conditional format rule type, see the list of types below.e.g. 0 \*/   
**$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 \*/   
**$Value**; /\* Optional; Specifies the criteria for the conditional formatting rule.  
Default is false. \*/   
**$StopIfTrue**) /\* Optional; if true, no rules with lower priority may be applied over this rule, when this rule is true.  
Default is false.e.g. false \*/ 

### Parameters

| Parameter | Description | Example | Flags |
|---|---|---|---|
| BookRef | The reference number for the workbook. | $ref |  |
| ConditionalFormattingRef | The reference number for the conditional formatting. | $ConditionalFormatting |  |
| FormatType | The conditional format rule type, see the list of types below. | 0 |  |
| 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 |  |
| Value | Specifies the criteria for the conditional formatting rule.   Default is false. |  | Optional |
| StopIfTrue | if true, no rules with lower priority may be applied over this rule, when this rule is true.   Default is false. | false | Optional |

### Result

Returns OK or error.

### Description

Adds a conditional formatting rule that highlights cells whose values correspond to the specified criteria.  
Available format types:  
  
| Name | Value | Description |
|---|---|---|
| CFORMAT\_BEGINWITH | 0 | highlights cells that begin with the given text |
| CFORMAT\_CONTAINSBLANKS | 1 | highlights cells that are completely blank |
| CFORMAT\_CONTAINSERRORS | 2 | highlights cells with formula errors |
| CFORMAT\_CONTAINSTEXT | 3 | highlights cells containing given text |
| CFORMAT\_DUPLICATEVALUES | 4 | highlights duplicated values |
| CFORMAT\_ENDSWITH | 5 | highlights cells ending with given text |
| CFORMAT\_EXPRESSION | 6 | highlights cells when the formula result is true |
| CFORMAT\_NOTCONTAINSBLANKS | 7 | highlights cells that are not blank |
| CFORMAT\_NOTCONTAINSERRORS | 8 | highlights cells without formula errors |
| CFORMAT\_NOTCONTAINSTEXT | 9 | highlights cells that do not contain given text |
| CFORMAT\_UNIQUEVALUES | 10 | highlights unique values in the range |

### Examples

Highlighting cells that begin With the given text:

 # add conditional format   
Set Variable \[ $cFormat ; Value: MBS ("[XL.Book.AddConditionalFormat](XLBookAddConditionalFormat.md)"; $book ) \]  
\#   
\# set font to be folder   
Set Variable \[ $font ; Value: MBS ("[XL.ConditionalFormat.Font](XLConditionalFormatFont.md)"; $book ; $cFormat ) \]  
Set Variable \[ $r ; Value: MBS ("[XL.Font.SetBold](XLFontSetBold.md)"; $book ; $font ; 1) \]  
\#   
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); 2; 10; 1; 1) \]  
Set Variable \[ $r ; Value: MBS ("XL.ConditionalFormating.AddRule"; $book ; $[CF](CF.md); 0 /\* BeginWith \*/; $cFormat ; "a") \]  
Highlighting alternating rows:

 # 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 ; MBS ("[XL.Color.Pack](XLColorPack.md)"; $book ; 240; 240; 240)) \]  
\#   
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); 4; 20; 1; 10) \]  
Set Variable \[ $r ; Value: MBS ("XL.ConditionalFormating.AddRule"; $book ; $[CF](CF.md); 6 /\* Expression \*/; $cFormat ; "=MOD(ROW(),2)=0") \]  
### See also

- [XL.Color.Pack](XLColorPack.md)
- [XL.ConditionalFormat.Font](XLConditionalFormatFont.md)
- [XL.ConditionalFormat.SetFillPattern](XLConditionalFormatSetFillPattern.md)
- [XL.ConditionalFormat.SetPatternBackgroundColor](XLConditionalFormatSetPatternBackgroundColor.md)
- [XL.ConditionalFormating.AddOpNumRule](XLConditionalFormatingAddOpNumRule.md)
- [XL.ConditionalFormating.AddOpStrRule](XLConditionalFormatingAddOpStrRule.md)
- [XL.ConditionalFormating.AddRange](XLConditionalFormatingAddRange.md)
- [XL.ConditionalFormating.AddTopRule](XLConditionalFormatingAddTopRule.md)
- [XL.Font.SetBold](XLFontSetBold.md)
- [XL.Sheet.AddConditionalFormatting](XLSheetAddConditionalFormatting.md)

### Example Databases

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

### 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.AddRange](XLConditionalFormatingAddRange.md) - [XL.ConditionalFormating.AddTimePeriodRule](XLConditionalFormatingAddTimePeriodRule.md)

[HTML Version](XLConditionalFormatingAddRule.shtml)