Platforms to show: All Mac Windows Linux Cross-Platform

XLConditionalFormattingMBS class

Type Topic Plugin Version macOS Windows Linux iOS Targets
class XL MBS XL Plugin 24.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
The class for a conditional formatting rule set.
Example 
// create a rule to make cells starting with A to be bold
Var cFormat As XLConditionalFormatMBS = book.addConditionalFormat()
cFormat.Font.bold = true

Var cf As XLConditionalFormattingMBS  = sheet.addConditionalFormatting()
cf.addRange(2, 10, 1, 1)
cf.addRule(cf.FormatTypeBeginWith, cFormat, "a")

This is an abstract class. You can't create an instance, but you can get one from various plugin functions.

  • 3 properties
  • 12 methods
    • method Add2ColorScaleFormulaRule(minColor as Integer, maxColor as Integer, minType as Integer = 2, minValue as String = "", maxType as Integer = 2, maxValue as String = "", stopIfTrue as Boolean = false)
    • method Add2ColorScaleRule(minColor as Integer, maxColor as Integer, minType as Integer = 0, minValue as Double = 0, maxType as Integer = 1, maxValue as Double = 0, stopIfTrue as Boolean = false)
    • method Add3ColorScaleFormulaRule(minColor as Integer, midColor as Integer, maxColor as Integer, minType as Integer = 2, minValue as String = "", midType as Integer = 2, midValue as String = "", maxType as Integer = 2, maxValue as String = "", stopIfTrue as Boolean = false)
    • method Add3ColorScaleRule(minColor as Integer, midColor as Integer, maxColor as Integer, minType as Integer = 0, minValue as Double = 0, midType as Integer = 5, midValue as Double = 0, maxType as Integer = 1, maxValue as Double = 0, stopIfTrue as Boolean = false)
    • method AddAboveAverageRule(format as XLConditionalFormatMBS, aboveAverage as Boolean = true, equalAverage as Boolean = false, stdDev as Integer = 0, stopIfTrue as Boolean = false)
    • method AddOpNumRule(op as Integer, format as XLConditionalFormatMBS, value1 as double, value2 as double = 0.0, stopIfTrue as Boolean = false)
    • method AddOpStrRule(op as Integer, format as XLConditionalFormatMBS, value1 as String = "", value2 as String = "", stopIfTrue as Boolean = false)
    • method AddRange(rowFirst as Integer, rowLast as Integer, colFirst as Integer, colLast as Integer)
    • method AddRule(FormatType as Integer, format as XLConditionalFormatMBS, value as String = "", stopIfTrue as Boolean = false)
    • method AddTimePeriodRule(format as XLConditionalFormatMBS, timePeriod as Integer, stopIfTrue as Boolean = false)
    • method AddTopRule(format as XLConditionalFormatMBS, value as Integer, bottom as Boolean = false, percent as Boolean = false, stopIfTrue as Boolean = false)
    • method Constructor   Private
  • 39 constants

Operators

Constant Value Description
FormatOperatorBeginsWith 10 'Begins with' operator
FormatOperatorBetween 6 'Between' operator
FormatOperatorContainsText 8 'Contains' operator
FormatOperatorEndsWith 11 'Ends with' operator
FormatOperatorEqual 2 'Equal to' operator
FormatOperatorGreaterThan 5 'Greater than' operator
FormatOperatorGreaterThanOrEqual 4 'Greater than or equal to' operator
FormatOperatorLessThan 0 'Less than' operator
FormatOperatorLessthanOrEqual 1 'Less than or equal to' operator
FormatOperatorNotBetween 7 'Not between' operator
FormatOperatorNotContains 9 'Does not contain' operator
FormatOperatorNotEqual 3 'Not equal to' operator

Time Periods

Constant Value Description
FormatTimePeriodLast7Days 0 a date in the last seven days
FormatTimePeriodLastMonth 1 a date occuring in the last calendar month
FormatTimePeriodLastWeek 2 a date occuring last week
FormatTimePeriodNextMonth 3 a date occuring in the next calendar mont
FormatTimePeriodNextWeek 4 a date occuring next week
FormatTimePeriodThisMonth 5 a date occuring in this calendar month
FormatTimePeriodThisWeek 6 a date occuring this week
FormatTimePeriodToday 7 today's date
FormatTimePeriodTomorrow 8 tomorrow's date
FormatTimePeriodYesterday 9 yesterday's date

Format Types

Constant Value Description
FormatTypeBeginWith 0 highlights cells that begin with the given text
FormatTypeContainsBlanks 1 highlights cells that are completely blank
FormatTypeContainsErrors 2 highlights cells with formula errors
FormatTypeContainsText 3 highlights cells containing given text
FormatTypeDuplicateValues 4 highlights duplicated values
FormatTypeEndsWith 5 highlights cells ending with given text
FormatTypeExpression 6 highlights cells when the formula result is true
FormatTypeNotContainsBlanks 7 highlights cells that are not blank
FormatTypeNotContainsErrors 8 highlights cells without formula errors
FormatTypeNotContainsText 9 highlights cells that do not contain given text
FormatTypeUniqueValues 10 highlights unique values in the range

Value Operators

Constant Value Description
FormatValueOperatorFormula 2 the minimum/midpoint/maximum value for the gradient is determined by a formula
FormatValueOperatorMax 1 indicates that the maximum value in the range shall be used as the maximum value for the gradient
FormatValueOperatorMin 0 indicates that the minimum value in the range shall be used as the minimum value for the gradient
FormatValueOperatorNumber 3 indicates that the minimum/midpoint/maximum value for the gradient is specified by a constant numeric value
FormatValueOperatorPercent 4 value indicates a percentage between the minimum and maximum values in the range shall be used as the minimum/midpoint/maximum value for the gradient
FormatValueOperatorPercentile 5 value indicates a percentile ranking in the range shall be used as the minimum/midpoint/maximum value for the gradient

This class has no sub classes.

Blog Entries

Release notes

Some methods using this class:

Some examples using this class:

Some related classes:


The items on this page are in the following plugins: MBS XL Plugin.


XLConditionalFormatMBS   -   XLCopyOptionsMBS


The biggest plugin in space...