# Conditional Formatting in Excel exports from FileMaker

**URL:** <https://the.fmsoup.org/t/conditional-formatting-in-excel-exports-from-filemaker/4082>\
**Category:** MBS Plugins\
**Tags:** plugins, excel-export, excel, mbs\
**Created:** [April 9, 2024, 6:03am UTC](https://the.fmsoup.org/t/conditional-formatting-in-excel-exports-from-filemaker/4082 "2024-04-09T06:03:06Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![MonkeybreadSoftware](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/monkeybreadsoftware/32/361_2.png) [@MonkeybreadSoftware](https://the.fmsoup.org/u/MonkeybreadSoftware)\
**Post date:** [April 9, 2024, 6:03am UTC](https://the.fmsoup.org/t/conditional-formatting-in-excel-exports-from-filemaker/4082/1 "2024-04-09T06:03:06Z")

</div>

For [MBS FileMaker Plugin](https://www.monkeybreadsoftware.com/filemaker/) 14.2 we include new [XL](https://www.mbsplugins.eu/component_XL.shtml) 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**

![](https://www.mbsplugins.de/image/ConditionalformattingTest1.png)

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](https://www.mbsplugins.eu/XLConditionalFormatingAddRule.shtml) 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.

```auto
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**

![](https://www.mbsplugins.de/image/ConditionalformattingTest2.png)

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](https://www.mbsplugins.eu/XLConditionalFormatingAdd2ColorScaleRule.shtml) or 3 colors with [XL.ConditionalFormating.Add3ColorScaleFormulaRule](https://www.mbsplugins.eu/XLConditionalFormatingAdd3ColorScaleFormulaRule.shtml), e.g. with a middle color.

```auto
# 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**

![](https://www.mbsplugins.de/image/ConditionalformattingTest3.png)

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.

```auto
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.

```auto
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")]

```

![](https://www.mbsplugins.de/image/ConditionalformattingTest4.png)

Please try the 14.2 plugin and see if this works for you. Please don't hesitate to contact us with your questions.