XL.Book.AddConditionalFormat
----------------------------

Adds a new conditional format to the workbook for using with conditional formatting rules.

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

MBS( "XL.Book.AddConditionalFormat"; BookRef ) 

**MBS**( **"XL.Book.AddConditionalFormat";** /\* Adds a new conditional format to the workbook for using with conditional formatting rules. \*/  
**$BookRef**) /\* The reference number for the workbook.e.g. $ref \*/ 

### Parameters

| Parameter | Description | Example |
|---|---|---|
| BookRef | The reference number for the workbook. | $ref |

### Result

Returns conditional format index number or error.

### Description

Adds a new conditional format to the workbook for using with conditional formatting rules.  
Requires LibXL 4.1 or newer and only for xlsx files.  
### Examples

Highlighting cells that begin With the given text:

 # add conditional format   
Set Variable \[ $cFormat ; Value: MBS ("XL.Book.AddConditionalFormat"; $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](XLConditionalFormatingAddRule.md)"; $book ; $[CF](CF.md); 0 /\* BeginWith \*/; $cFormat ; "a") \]  
Highlight cells based on other cell with formula:

 # Make date format   
Set Variable \[ $dateFormat ; Value: MBS ( "[XL.Book.AddFormat](XLBookAddFormat.md)"; $Book ) \]  
Set Variable \[ $r ; Value: MBS ( "[XL.Format.SetNumFormat](XLFormatSetNumFormat.md)"; $Book ; $dateFormat ; 14) \]  
\#   
\# write two dates   
Set Variable \[ $r ; Value: MBS ("[XL.Sheet.CellWriteDate](XLSheetCellWriteDate.md)"; $book ; $sheet ; 3; 3; Date ( 3 ; 4 ; 2020 ); $dateFormat ) \]  
Set Variable \[ $r ; Value: MBS ("[XL.Sheet.CellWriteDate](XLSheetCellWriteDate.md)"; $book ; $sheet ; 3; 4; Date ( 4 ; 5 ; 2021 ); $dateFormat ) \]  
\#   
\# add conditional format   
Set Variable \[ $cFormat ; Value: MBS ("XL.Book.AddConditionalFormat"; $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)) \]  
\#   
\# define formula to change background if second one is bigger   
Set Variable \[ $[CF](CF.md) ; Value: MBS ("[XL.Sheet.AddConditionalFormatting](XLSheetAddConditionalFormatting.md)"; $book ; $sheet ; 2; 20; 4; 4) \]  
\# newer LibXL 5.1 needs range in AddConditionalFormatting call and older LibXL needs AddRange call   
// Set Variable \[ $r ; Value: MBS ("[XL.ConditionalFormating.AddRange](XLConditionalFormatingAddRange.md)"; $book ; $[CF](CF.md); 2; 20; 4; 4) \]  
\# the 3 is relative to the first row of the range   
Set Variable \[ $r ; Value: MBS ("[XL.ConditionalFormating.AddRule](XLConditionalFormatingAddRule.md)"; $book ; $[CF](CF.md); 6 /\* Expression \*/; $cFormat ; "=$E3 >$D3 ") \]  
### See also

- [XL.Book.ConditionalFormat](XLBookConditionalFormat.md)
- [XL.Book.ConditionalFormatSize](XLBookConditionalFormatSize.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.AddRule](XLConditionalFormatingAddRule.md)
- [XL.Format.SetNumFormat](XLFormatSetNumFormat.md)
- [XL.Sheet.AddConditionalFormatting](XLSheetAddConditionalFormatting.md)

### Release notes

- **Version 16.1**
    - Changed [XL.Book.AddConditionalFormat](https://www.mbsplugins.eu/XLBookAddConditionalFormat.shtml) function to take area as parameters.
- **Version 14.2**
    - Added new [XL](https://www.mbsplugins.eu/component_XL.shtml) functions to create conditional formatting including [XL.Book.AddConditionalFormat](https://www.mbsplugins.eu/XLBookAddConditionalFormat.shtml) and [XL.Sheet.AddConditionalFormatting](https://www.mbsplugins.eu/XLSheetAddConditionalFormatting.shtml).

### Example Databases

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

### Blog Entries

- [MBS FileMaker Plugin, version 16.1pr1](https://www.mbsplugins.de/archive/2026-02-01/MBS_FileMaker_Plugin_version_1/monkeybreadsoftware_blog_filemaker)
- [Neues MBS Plugin 14.2 für Claris FileMaker](https://www.mbsplugins.de/archive/2024-05-14/Neues_MBS_Plugin_142_für_Clar/monkeybreadsoftware_blog_filemaker)
- [MBS Plugin 14.2 for Claris FileMaker](https://www.mbsplugins.de/archive/2024-05-14/MBS_Plugin_142_for_Claris_File/monkeybreadsoftware_blog_filemaker)
- [Conditional Formatting in Excel exports from FileMaker](https://www.mbsplugins.de/archive/2024-04-08/Conditional_Formatting_in_Exce/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Plugin, version 14.2pr1](https://www.mbsplugins.de/archive/2024-04-02/MBS_FileMaker_Plugin_version_1/monkeybreadsoftware_blog_filemaker)

Created 30th March 2024 , last changed 30th January 2026

  
[X509.Write](X509Write.md) - [XL.Book.AddCustomNumFormat](XLBookAddCustomNumFormat.md)

[HTML Version](XLBookAddConditionalFormat.shtml)