Components All New MacOS Windows Linux iOS
Examples Mac & Win Server Client Guides Statistic FMM Blog Deprecated Old


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 14.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes ✅ Yes
MBS( "XL.ConditionalFormating.AddOpNumRule"; bookRef; ConditionalFormattingRef; Operator; ConditionalFormatRef; Value1 { ; Value2; StopIfTrue } )   More


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
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 for adding conditional formats.
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.


Returns OK or error.


Adds a conditional formatting rule that highlights cells whose values are compared with a calculated result, using an operator.
CFOPERATOR_LESSTHAN0Less than' operator
CFOPERATOR_LESSTHANOREQUAL1Less than or equal to' operator
CFOPERATOR_EQUAL2Equal to' operator
CFOPERATOR_NOTEQUAL3Not equal to' operator
CFOPERATOR_GREATERTHANOREQUAL4Greater than or equal to' operator
CFOPERATOR_GREATERTHAN5Greater than' operator
CFOPERATOR_BETWEEN6Between' operator
CFOPERATOR_NOTBETWEEN7Not between' operator
CFOPERATOR_NOTCONTAINS9Does not contain' operator
CFOPERATOR_BEGINSWITH10Begins with' operator
CFOPERATOR_ENDSWITH11Ends with' operator


Highlighting cells that more than the specified value:

# 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; 42 /* light green */) ]
# add conditional format to color cells based on value
Set Variable [ $CF ; Value: MBS("XL.Sheet.AddConditionalFormatting"; $book; $sheet) ]
Set Variable [ $r ; Value: MBS("XL.ConditionalFormating.AddRange"; $book; $CF; 3; 10; 2; 2) ]
Set Variable [ $r ; Value: MBS("XL.ConditionalFormating.AddOpNumRule"; $book; $CF; 5 /* GreaterThan */; $cFormat; 90) ]

See also

Example Databases

Blog Entries

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

XL.ConditionalFormating.AddAboveAverageRule - XL.ConditionalFormating.AddOpStrRule