Platforms to show: All Mac Windows Linux Cross-Platform

Back to XLConditionalFormattingMBS class.

XLConditionalFormattingMBS.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)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 24.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a conditional formatting rule that creates a gradated 2-color scale on the cells.

minColor: the color for minimum;
maxColor: the color for maximum;
minType: the type of minValue, see values above;
minValue: the formula expression for minimum;
maxType: the type of maxValue, see values above;
maxValue: the formula expression for maximum;
stopIfTrue: if true, no rules with lower priority may be applied over this rule, when this rule is true.

XLConditionalFormattingMBS.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)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 24.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a conditional formatting rule that creates a gradated 2-color scale on the cells.
Example 
// creating a gradated Color scale on the cells
Var cf As XLConditionalFormattingMBS  = sheet.addConditionalFormatting()
cf.addRange(3, 10, 2, 2)
cf.add2ColorScaleRule(book.PackColor(&hFF, &h71, &h28), book.PackColor(&hFF, &hEF, &h9C))

minColor: the color for minimum;
maxColor: the color for maximum;
minType: the type of minValue, see values below;
minValue: the numeric value for minimum;
maxType: the type of maxValue, see values below;
maxValue: the numeric value for maximum;
stopIfTrue: if true, no rules with lower priority may be applied over this rule, when this rule is true.

Some examples using this method:

XLConditionalFormattingMBS.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)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 24.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a conditional formatting rule that creates a gradated 3-color scale on the cells.

minColor: the color for minimum;
minColor: the color for midpoint;
maxColor: the color for maximum;
minType: the type of minValue, see values above;
minValue: the formula expression for minimum;
midType: the type of midValue, see values above;
midValue: the formula expression for midpoint;
maxType: the type of maxValue, see values above;
maxValue: the formula expression for maximum;
stopIfTrue: if true, no rules with lower priority may be applied over this rule, when this rule is true.

XLConditionalFormattingMBS.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)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 24.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a conditional formatting rule that creates a gradated 3-color scale on the cells.

minColor: the color for minimum;
minColor: the color for midpoint;
maxColor: the color for maximum;
minType: the type of minValue, see values above;
minValue: the numeric value for minimum;
midType: the type of midValue, see values above;
midValue: the numeric value for midpoint;
maxType: the type of maxValue, see values above;
maxValue: the numeric value for maximum;
stopIfTrue: if true, no rules with lower priority may be applied over this rule, when this rule is true.

XLConditionalFormattingMBS.AddAboveAverageRule(format as XLConditionalFormatMBS, aboveAverage as Boolean = true, equalAverage as Boolean = false, stdDev as Integer = 0, stopIfTrue as Boolean = false)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 24.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a conditional formatting rule that highlights cells that are above or below the average for all values in the range.

Format: the conditional format used for highlighting cells, use the addConditionalFormat() for adding conditional formats;
aboveAverage: true for [above average] rule, false for [below average] rule;
equalAverage: true for inclusive of the average itself, false for exclusive of that value, valid only for [above average] rule;
stdDev: the number of standard deviations to include above or below the average, valid only for [above average] rule;
stopIfTrue: if true, no rules with lower priority may be applied over this rule, when this rule is true.

XLConditionalFormattingMBS.AddOpNumRule(op as Integer, format as XLConditionalFormatMBS, value1 as double, value2 as double = 0.0, stopIfTrue as Boolean = false)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 24.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a conditional formatting rule that highlights cells whose values are compared with a calculated result, using an operator.
Example 
// highlighting cells that more than the specified value

const COLOR_LIGHTGREEN = 42

Var cFormat As XLConditionalFormatMBS = book.addConditionalFormat()
cFormat.FillPattern = cFormat.FillPatternSolid
cFormat.PatternBackgroundColor = COLOR_LIGHTGREEN

Var cf As XLConditionalFormattingMBS  = sheet.addConditionalFormatting()
cf.addRange(3, 10, 2, 2)
cf.addOpNumRule(cf.FormatOperatorGreaterThan, cFormat, 90)

op: the operator in the conditional formatting rule, see the list of operators below;
Format: the conditional format used for highlighting cells, use the addConditionalFormat() for adding conditional formats;
value1: the numeric value for the expression with the specified operator;
value2: the numeric second optional value for the expression only for some operators;
stopIfTrue: if true, no rules with lower priority may be applied over this rule, when this rule is true.

Some examples using this method:

XLConditionalFormattingMBS.AddOpStrRule(op as Integer, format as XLConditionalFormatMBS, value1 as String = "", value2 as String = "", stopIfTrue as Boolean = false)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 24.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a conditional formatting rule that highlights cells whose values are compared with a calculated result, using an operator.

op: the operator in the conditional formatting rule, see the list of operators above;
Format: the conditional format used for highlighting cells, use the addConditionalFormat() for adding conditional formats;
value1: the string value for the expression with the specified operator;
value2: the string second optional value for the expression only for some operators;
stopIfTrue: if true, no rules with lower priority may be applied over this rule, when this rule is true.

XLConditionalFormattingMBS.AddRange(rowFirst as Integer, rowLast as Integer, colFirst as Integer, colLast as Integer)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 24.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a range to these conditional formatting rules.

XLConditionalFormattingMBS.AddRule(FormatType as Integer, format as XLConditionalFormatMBS, value as String = "", stopIfTrue as Boolean = false)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 24.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a conditional formatting rule that highlights cells whose values correspond to the specified criteria.
Example 
// highlighting alternating rows
book.RgbMode = true

Var cFormat As XLConditionalFormatMBS = book.addConditionalFormat()
cFormat.FillPattern = cFormat.FillPatternSolid
cFormat.PatternBackgroundColor = book.PackColor(240, 240, 240)

Var cFormatting As XLConditionalFormattingMBS = sheet.addConditionalFormatting()
cFormatting.addRange(4, 20, 1, 10)
cFormatting.addRule(cFormatting.FormatTypeExpression, cFormat, "=MOD(ROW(),2)=0")

For row As Integer = 4 To 19
  For col As Integer = 1 To 9
    Call sheet.WriteNumber(row, col, row + col)
  Next
Next

FormatType: the conditional format rule type, see the list of types below, see constants;
format: the conditional format used for highlighting cells, use the addConditionalFormat() for adding conditional formats;
value: specifies the criteria for the conditional formatting rule;
stopIfTrue: if true, no rules with lower priority may be applied over this rule, when this rule is true;

Some examples using this method:

XLConditionalFormattingMBS.AddTimePeriodRule(format as XLConditionalFormatMBS, timePeriod as Integer, stopIfTrue as Boolean = false)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 24.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a conditional formatting rule that highlights cells containing dates in the specified time period.

Format: the conditional format used for highlighting cells, use the addConditionalFormat() for adding conditional formats;
timePeriod: the applicable time period, see the list of values below;
stopIfTrue: if true, no rules with lower priority may be applied over this rule, when this rule is true.

XLConditionalFormattingMBS.AddTopRule(format as XLConditionalFormatMBS, value as Integer, bottom as Boolean = false, percent as Boolean = false, stopIfTrue as Boolean = false)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 24.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a conditional formatting rule that highlights cells whose values fall in the [top N] or [bottom N] bracket.

Format - the conditional format used for highlighting cells, use the addConditionalFormat() for adding conditional formats;
value: specifies the [top N] or [bottom N] bracket;
bottom: true for a [bottom N] rule, false for a [top N] rule
percent: true for a percent top/bottom rule;
stopIfTrue: if true, no rules with lower priority may be applied over this rule, when this rule is true.

XLConditionalFormattingMBS.Constructor   Private

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 24.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
The private constructor.

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


The biggest plugin in space...