Monkeybread Software - MBS FileMaker Plugin: XL.ConditionalFormating.AddOpNumRule

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

XL.ConditionalFormating.AddOpNumRule

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

Parameters

Parameter Description Example Flags

BookRef The reference number for the workbook. $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. $ConditionalFormat

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. Optional

Parameter	Description	Example	Flags
BookRef	The reference number for the workbook.	$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.	$ConditionalFormat
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.		Optional

Result

Returns OK or error.

Description

Adds a conditional formatting rule that highlights cells whose values are compared with a calculated result, using an operator.

Name	Value	Description
CFOPERATOR_LESSTHAN	0	Less than' operator
CFOPERATOR_LESSTHANOREQUAL	1	Less than or equal to' operator
CFOPERATOR_EQUAL	2	Equal to' operator
CFOPERATOR_NOTEQUAL	3	Not equal to' operator
CFOPERATOR_GREATERTHANOREQUAL	4	Greater than or equal to' operator
CFOPERATOR_GREATERTHAN	5	Greater than' operator
CFOPERATOR_BETWEEN	6	Between' operator
CFOPERATOR_NOTBETWEEN	7	Not between' operator
CFOPERATOR_CONTAINSTEXT	8	Contains' operator
CFOPERATOR_NOTCONTAINS	9	Does not contain' operator
CFOPERATOR_BEGINSWITH	10	Begins with' operator
CFOPERATOR_ENDSWITH	11	Ends with' operator

Examples

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) ]
Highlighting cells that more than the specified value:

<?xml version="1.0" encoding="UTF-8"?>
<fmxmlsnippet type="FMObjectList">
  <Step enable="True" id="89" name="# (comment)">
    <Text> add conditional format</Text>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.Book.AddConditionalFormat"; $book) </Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$cFormat</Name>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.ConditionalFormat.SetFillPattern"; $book; $cFormat; 1 /* solid */) </Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$r</Name>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.ConditionalFormat.SetPatternBackgroundColor"; $book; $cFormat; 42 /* light green */) </Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$r</Name>
  </Step>
  <Step enable="True" id="89" name="# (comment)"/>
  <Step enable="True" id="89" name="# (comment)">
    <Text> add conditional format to color cells based on value</Text>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.Sheet.addConditionalFormatting"; $book; $sheet) </Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$cf</Name>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.ConditionalFormating.AddRange"; $book; $cf; 3; 10; 2; 2) </Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$r</Name>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.ConditionalFormating.AddOpNumRule"; $book; $cf; 5 /* GreaterThan */; $cFormat; 90) </Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$r</Name>
  </Step>
</fmxmlsnippet>

Example Databases

XL/Conditional Formatting

Blog Entries

Conditional Formatting in Excel exports from FileMaker

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

XL.ConditionalFormating.AddAboveAverageRule - XL.ConditionalFormating.AddOpStrRule

💬 Ask a question or report a problem