For MBS FileMaker Plugin 14.2 we include new XL functions to create conditional formatting in an Excel file created in a script. While you can always load an existing document with conditional formatting or diagrams and just fill the cells with data, we now can create the rules for conditional formatting in a script. Let us show you four examples from our example file.
Highlighting cells that begin With the given Text
This example defines a conditional format with a bold font. The conditional formatting is applied for the cells in the range B3:B11 to highlight all cells that begin with 'a'. The XL.ConditionalFormating.AddRule function allows you to define rules with various conditions like to highlight empty cells, duplicate or unique values, values starting or ending with a value or by an expression.
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") ]
Creating a gradated Color scale on the cells
This example creates a gradated color scale for the range C4:C11. All cells within the range have values and these are read as percentages and applied to the color range from yellow to red. We can use two colors with XL.ConditionalFormating.Add2ColorScaleRule or 3 colors with XL.ConditionalFormating.Add3ColorScaleFormulaRule, e.g. with a middle color.
# 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.Add2ColorScaleRule"; $book; $cf; MBS( "XL.Color.Pack"; $book; 255; 133; 40 ); MBS( "XL.Color.Pack"; $book; 255; 239; 156 )) ]
Highlighting cells that more than the specified value
This example highlights cells whose values are greater than the specified value (90) with the light green background in the range C4:C11. It's possible to use any operator from the list in the documentation like comparators or text operations like contains or begins/ends with.
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 alternating rows
This example highlights alternating rows (banded rows) and makes the data in a worksheet easier to scan. It's possible to do this with the formula expression "=MOD(ROW(),2)=0" in a conditional formatting rule. If you prefer alternate columns, please use this formula: "=MOD(COLUMN(),2)=0". As you notice the formula should return a boolean for whether the rule applies or not and can reference Column and Row functions. You can use a formula like "=MOD(COLUMN() + ROW(); 2) = 0" to get the checkmate pattern. All formulas must always be typed in English as they get parsed and stored as tokens. When the user reads them, they may see them in a different language.
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") ]
Please try the 14.2 plugin and see if this works for you. Please don't hesitate to contact us with your questions.