| Components | All | New | MacOS | Windows | Linux | iOS | ||||
| Examples | Mac & Win | Server | Client | Guides | Statistic | FMM | Blog | Deprecated | Old | |
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 | 14.2 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
Parameters
| Parameter | Description | Example | Flags |
|---|---|---|---|
| bookRef | The reference to the workbook. Please use XL.LoadBook to load a file. | $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 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"; $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") ]
Highlighting alternating rows:
# 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)) ]
#
Set Variable [ $CF ; Value: MBS("XL.Sheet.AddConditionalFormatting"; $book; $sheet) ]
Set Variable [ $r ; Value: MBS("XL.ConditionalFormating.AddRange"; $book; $CF; 4; 20; 1; 10) ]
Set Variable [ $r ; Value: MBS("XL.ConditionalFormating.AddRule"; $book; $CF; 6 /* Expression */; $cFormat; "=MOD(ROW(),2)=0") ]
See also
- XL.Book.AddConditionalFormat
- XL.Color.Pack
- XL.ConditionalFormat.SetPatternBackgroundColor
- XL.ConditionalFormating.AddOpNumRule
- XL.ConditionalFormating.AddOpStrRule
- XL.ConditionalFormating.AddRange
- XL.ConditionalFormating.AddTopRule
- XL.Font.SetBold
- XL.LoadBook
- XL.Sheet.AddConditionalFormatting
Example Databases
Blog Entries
Created 30th March 2024, last changed 31st March 2024
XL.ConditionalFormating.AddRange - XL.ConditionalFormating.AddTimePeriodRule
