For MBS Xojo XL Plugin in version 24.2 we include new XL classes to create conditional formatting in an Excel file created in code. 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.
Please check the XLConditionalFormatMBS and XLConditionalFormattingMBS classes. The first defines the format to apply and the second one defines the rules.
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 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.
Dim cFormat As XLConditionalFormatMBS = book.addConditionalFormat()
cFormat.Font.bold = true
Dim cf As XLConditionalFormattingMBS = sheet.addConditionalFormatting()
cf.addRange(2, 10, 1, 1)
cf.addRule(cf.FormatTypeBeginWith, 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 Add2ColorScaleRule or 3 colors with Add3ColorScaleFormulaRule, e.g. with a middle color.
Dim cf As XLConditionalFormattingMBS = sheet.addConditionalFormatting()
cf.addRange(3, 10, 2, 2)
cf.add2ColorScaleRule(book.PackColor(&hFF, &h71, &h28), book.PackColor(&hFF, &hEF, &h9C))
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.
Const COLOR_LIGHTGREEN = 42
Dim cFormat As XLConditionalFormatMBS = book.addConditionalFormat()
cFormat.FillPattern = cFormat.FillPatternSolid
cFormat.PatternBackgroundColor = COLOR_LIGHTGREEN
Dim cf As XLConditionalFormattingMBS = sheet.addConditionalFormatting()
cf.addRange(3, 10, 2, 2)
cf.addOpNumRule(cf.FormatOperatorGreaterThan, 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.
book.RgbMode = True
Dim cFormat As XLConditionalFormatMBS = book.addConditionalFormat()
cFormat.FillPattern = cFormat.FillPatternSolid
cFormat.PatternBackgroundColor = book.PackColor(240, 240, 240)
Dim cFormatting As XLConditionalFormattingMBS = sheet.addConditionalFormatting()
cFormatting.addRange(4, 20, 1, 10)
cFormatting.addRule(cFormatting.FormatTypeExpression, cFormat, "=MOD(ROW(),2)=0")
Please try the 24.2 version of MBS Xojo XL Plugin and see if this works for you. Please don’t hesitate to contact us with your questions.