| Components | All | New | MacOS | Windows | Linux | iOS | ||||
| Examples | Mac & Win | Server | Client | Guides | Statistic | FMM | Blog | Deprecated | Old | |
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 | 14.2 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
MBS( "XL.Book.AddConditionalFormat"; BookRef ) More
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"; $book; $cFormat) ]
Set Variable [ $r ; Value: MBS("XL.Font.SetBold"; $book; $font; 1) ]
#
Set Variable [ $CF ; Value: MBS("XL.Sheet.AddConditionalFormatting"; $book; $sheet) ]
Set Variable [ $r ; Value: MBS("XL.ConditionalFormating.AddRange"; $book; $CF; 2; 10; 1; 1) ]
Set Variable [ $r ; Value: MBS("XL.ConditionalFormating.AddRule"; $book; $CF; 0 /* BeginWith */; $cFormat; "a") ]
Set Variable [ $cFormat ; Value: MBS("XL.Book.AddConditionalFormat"; $book) ]
#
# set font to be folder
Set Variable [ $font ; Value: MBS("XL.ConditionalFormat.Font"; $book; $cFormat) ]
Set Variable [ $r ; Value: MBS("XL.Font.SetBold"; $book; $font; 1) ]
#
Set Variable [ $CF ; Value: MBS("XL.Sheet.AddConditionalFormatting"; $book; $sheet) ]
Set Variable [ $r ; Value: MBS("XL.ConditionalFormating.AddRange"; $book; $CF; 2; 10; 1; 1) ]
Set Variable [ $r ; Value: MBS("XL.ConditionalFormating.AddRule"; $book; $CF; 0 /* BeginWith */; $cFormat; "a") ]
Highlight cells based on other cell with formula:
# Make date format
Set Variable [ $dateFormat ; Value: MBS( "XL.Book.AddFormat"; $Book ) ]
Set Variable [ $r ; Value: MBS( "XL.Format.SetNumFormat"; $Book; $dateFormat; 14) ]
#
# write two dates
Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteDate"; $book; $sheet; 3; 3; Date ( 3 ; 4 ; 2020 ); $dateFormat) ]
Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteDate"; $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"; $book; $cFormat; 1 /* solid */) ]
Set Variable [ $r ; Value: MBS("XL.ConditionalFormat.SetPatternBackgroundColor"; $book; $cFormat; MBS("XL.Color.Pack"; $book; 240; 240; 240)) ]
#
# define formula to change background if second one is bigger
Set Variable [ $CF ; Value: MBS("XL.Sheet.AddConditionalFormatting"; $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"; $book; $CF; 2; 20; 4; 4) ]
# the 3 is relative to the first row of the range
Set Variable [ $r ; Value: MBS("XL.ConditionalFormating.AddRule"; $book; $CF; 6 /* Expression */; $cFormat; "=$E3>$D3") ]
Set Variable [ $dateFormat ; Value: MBS( "XL.Book.AddFormat"; $Book ) ]
Set Variable [ $r ; Value: MBS( "XL.Format.SetNumFormat"; $Book; $dateFormat; 14) ]
#
# write two dates
Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteDate"; $book; $sheet; 3; 3; Date ( 3 ; 4 ; 2020 ); $dateFormat) ]
Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteDate"; $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"; $book; $cFormat; 1 /* solid */) ]
Set Variable [ $r ; Value: MBS("XL.ConditionalFormat.SetPatternBackgroundColor"; $book; $cFormat; MBS("XL.Color.Pack"; $book; 240; 240; 240)) ]
#
# define formula to change background if second one is bigger
Set Variable [ $CF ; Value: MBS("XL.Sheet.AddConditionalFormatting"; $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"; $book; $CF; 2; 20; 4; 4) ]
# the 3 is relative to the first row of the range
Set Variable [ $r ; Value: MBS("XL.ConditionalFormating.AddRule"; $book; $CF; 6 /* Expression */; $cFormat; "=$E3>$D3") ]
See also
- XL.Book.ConditionalFormat
- XL.Book.ConditionalFormatSize
- XL.ConditionalFormat.SetFillPattern
- XL.ConditionalFormat.SetPatternBackgroundColor
- XL.ConditionalFormating.AddOpNumRule
- XL.ConditionalFormating.AddOpStrRule
- XL.ConditionalFormating.AddRange
- XL.ConditionalFormating.AddRule
- XL.Format.SetNumFormat
- XL.Sheet.AddConditionalFormatting
Release notes
- Version 16.1
- Changed XL.Book.AddConditionalFormat function to take area as parameters.
- Version 14.2
- Added new XL functions to create conditional formatting including XL.Book.AddConditionalFormat and XL.Sheet.AddConditionalFormatting.
Example Databases
Blog Entries
- MBS FileMaker Plugin, version 16.1pr1
- Neues MBS Plugin 14.2 für Claris FileMaker
- MBS Plugin 14.2 for Claris FileMaker
- Conditional Formatting in Excel exports from FileMaker
- MBS FileMaker Plugin, version 14.2pr1
Created 30th March 2024, last changed 30th January 2026
