Exposes properties which control the coloring of a WorksheetCell based on the cell's value as relative to minimum, midpoint, and maximum threshold values.

This class support either a 2-color or 3-color scale, as determined by the value of the colorScaleType parameter of the constructor.

For 2-color scales, cells in the associated range are colored based on how their value relates to the minimum and maximum thresholds.

For 3-color scales, cells in the associated range are colored based on how their value relates to the minimum, midpoint, and maximum thresholds.

This class supports a maximum of three separate and distinct color thresholds.

Specifying only one threshold is not supported and may result in unexpected behavior.

If this instance is created as a 2-color scale, the midpointThreshold property is not applicable. Attempting to set a property on the object returned from the MidpointThreshold property in this case will cause an exception to be thrown.

See

Hierarchy

Hierarchy

Constructors

Properties

$type: Type
$t: Type
nextHashCode: number

Accessors

  • get colorScaleType(): ColorScaleType
  • Returns a ColorScaleType value which identifies this instance as a 2-color or 3-color scale.

    A ColorScaleConditionalFormat instance which is created with the 'TwoColor' value does not support the midpointThreshold; attempting to set a property on the object retuned from the MidpointThreshold property will cause an exception to be thrown.

    Returns ColorScaleType

  • get conditionType(): FormatConditionType
  • Returns a FormatConditionType value which identifies the type of condition for this instance.

    Returns FormatConditionType

  • get formula(): Formula
  • Returns the formula which determines the values to which this condition applies. Note: the formula should return a boolean result. When the formula is applied to cells in the region if the formula returns false then those cells will not be included in the formatting.

    See

    setFormula

    Returns Formula

  • get maximumThreshold(): ColorScaleCriterion
  • Returns a ColorScaleCriterion object which defines the properties of the maximum threshold for this instance.

    The following value types are not valid for the maximum threshold, and will cause an exception to be thrown:

    • LowestValue
    • AutomaticMinimum
    • AutomaticMaximum

    See

    Returns ColorScaleCriterion

  • get midpointThreshold(): ColorScaleCriterion
  • Returns a ColorScaleCriterion object which defines the properties of the midpoint threshold for this instance.

    The midpoint threshold is only supported for a ColorScaleConditionalFormat instance with a value of 'ThreeColor' specified for the colorScaleType parameter.

    Attempting to set a property on the instance returned from this property will cause an exception to be thrown if this instance was created as a 2-color scale.

    The following value types are not valid for the midpoint threshold, and will cause an exception to be thrown:

    • LowestValue
    • HighestValue
    • AutomaticMinimum
    • AutomaticMaximum

    See

    Returns ColorScaleCriterion

  • get minimumThreshold(): ColorScaleCriterion
  • Returns a ColorScaleCriterion object which defines the properties of the minimum threshold for this instance.

    The minimum threshold defines the criteria for the lowest or minimum value across the associated range.

    For a 2-color scale, the maximumThreshold defines the next "stop"; for a 3-color scale, the midpointThreshold property defines the next stop.

    The following value types are not valid for the minimum threshold, and will cause an exception to be thrown:

    • HighestValue
    • AutomaticMinimum
    • AutomaticMaximum

    See

    Returns ColorScaleCriterion

  • get priority(): number
  • Returns or sets a value which determines the order of evaluation when multiple conditional formatting rules exist.

    By default, conditions are evaluated in the order in which they appear within the containing collection.

    This property makes it possible to weight one condition over another, essentially overriding the condition's ordinal position within the collection.

    A lower number means a higher priority; for example, a condition with a priority of 1 is evaluated before one with a priority of 2.

    If a cell value meets more than one condition, the format defined by the first condition to be evaluated takes precedence.

    Setting the priority on one condition may cause the value of other conditions in the collection to be changed to ensure that each condition has a unique value.

    Returns number

  • set priority(a: number): void
  • Parameters

    • a: number

    Returns void

  • get regions(): ReadOnlyCollection$1<WorksheetRegion>
  • Returns the WorksheetRegion which identifies the cell range with which this condition is associated.

    Returns ReadOnlyCollection$1<WorksheetRegion>

  • get stopIfTrue(): boolean
  • Returns or sets a boolean indicating whether additional formatting rules are evaluated if this rule evaluates to true. Applicable only in the case where the containing collection contains more than one element.

    If a cell value meets more than one condition, the format defined by the first condition takes precedence. Individual format properties are "merged" during the resolution process; for example, if one format has red borders and another has a red foreground color, a cell value which meets both conditions may be formatted with both red borders and a red foreground color.

    This property makes it possible to short-circuit the resolution process, so that no other conditional formats are applied after the first condition evaluates to true.

    Using the above example, assume StopIfTrue is set to a value of true on the conditional format with red borders, a second conditional format exists with a red foreground color, and a cell value meets both conditions. Because the StopIfTrue setting prevents additional formats from being evaluated, the cell will not show a red foreground color, whereas with StopIfTrue being set to false, it would show both red borders and a red foreground color.

    In addition to preventing subsequent conditions from being evaluated, the order in which conditions are evaluated can be controlled using the priority property.

    Returns boolean

  • set stopIfTrue(a: boolean): void
  • Parameters

    • a: boolean

    Returns void

  • get workbook(): Workbook
  • Returns a reference to the associated workbook.

    Returns Workbook

  • get worksheet(): Worksheet
  • Returns a reference to the associated worksheet.

    Returns Worksheet

Methods

  • Parameters

    • other: any

    Returns boolean

  • Sets the priority value for this rule such that it is the first condition to be evaluated. Applicable only in the case where there is more than one element contained within the associated collection.

    When multiple conditions exist in the same collection, the order in which they are evaluated has bearing on which format is applied to cells which meet those conditions.

    This method ensures that the associated condition is the first one to be evaluated.

    To make a condition the last one to be evaluated, use the setLastPriority method.

    See

    Returns void

  • Assigns a new Formula to the formula property. Note: the formula should return a boolean result. When the formula is applied to cells in the region if the formula returns false then those cells will not be included in the formatting.

    See

    formula

    Parameters

    • formula: string

      The string representation of the formula.

    • Optional cellReferenceMode: CellReferenceMode

      A CellReferenceMode value which specifies whether the 'formula' parameter should be interpreted as an A1 or R1C1 expression. This parameter is optional and defaults to null, in which case the Workbook.cellReferenceMode property is used.

    Returns void

  • Sets the priority value for this rule such that it is the last condition to be evaluated. Applicable only in the case where there is more than one element contained within the associated ConditionalFormatCollection.

    Just as the setFirstPriority method ensures that the associated condition is evalulated first, the SetLastPriority method ensures that the associated condition is evaluated last.

    Calling this method on more than one condition essentially replaces the previously designated condition as the last one to be evaluated with this one.

    See

    Returns void

  • Replaces the existing regions with new ones

    Throws

    ArgumentException If regionAddress is invalid or contains a region from another worksheet

    Parameters

    • regionAddress: string

      A string with one or more comma separated region address

    • cellReferenceMode: null | CellReferenceMode

      An optional cell reference mode

    Returns void

  • Parameters

    • item1: any
    • item2: any

    Returns number

  • Parameters

    • item1: any
    • item2: any

    Returns number

  • Parameters

    • item1: any
    • item2: any

    Returns boolean

  • Parameters

    • a: any
    • b: any

    Returns boolean

  • Parameters

    • a: any
    • b: any

    Returns boolean