ExcelScript.ConditionalFormat interface

Package:

ExcelScript

An object encapsulating a conditional format's range, format, rule, and other properties.

Methods

delete()	Deletes this conditional format.
getCellValue()	Returns the cell value conditional format properties if the current conditional format is a CellValue type.
getColorScale()	Returns the color scale conditional format properties if the current conditional format is a ColorScale type.
getCustom()	Returns the custom conditional format properties if the current conditional format is a custom type.
getDataBar()	Returns the data bar properties if the current conditional format is a data bar.
getIconSet()	Returns the icon set conditional format properties if the current conditional format is an IconSet type.
getId()	The priority of the conditional format in the current ConditionalFormatCollection.
getPreset()	Returns the preset criteria conditional format. See ExcelScript.PresetCriteriaConditionalFormat for more details.
getPriority()	The priority (or index) within the conditional format collection that this conditional format currently exists in. Changing this also changes other conditional formats' priorities, to allow for a contiguous priority order. Use a negative priority to begin from the back. Priorities greater than the bounds will get and set to the maximum (or minimum if negative) priority. Also note that if you change the priority, you have to re-fetch a new copy of the object at that new priority location if you want to make further changes to it.
getRange()	Returns the range to which the conditional format is applied. If the conditional format is applied to multiple ranges, then this method returns undefined.
getRanges()	Returns the RangeAreas, comprising one or more rectangular ranges, to which the conditional format is applied.
getStopIfTrue()	If the conditions of this conditional format are met, no lower-priority formats shall take effect on that cell. Value is null on data bars, icon sets, and color scales as there's no concept of StopIfTrue for these.
getTextComparison()	Returns the specific text conditional format properties if the current conditional format is a text type. For example, to format cells matching the word "Text".
getTopBottom()	Returns the top/bottom conditional format properties if the current conditional format is a TopBottom type. For example, to format the top 10% or bottom 10 items.
getType()	A type of conditional format. Only one can be set at a time.
setPriority(priority)	The priority (or index) within the conditional format collection that this conditional format currently exists in. Changing this also changes other conditional formats' priorities, to allow for a contiguous priority order. Use a negative priority to begin from the back. Priorities greater than the bounds will get and set to the maximum (or minimum if negative) priority. Also note that if you change the priority, you have to re-fetch a new copy of the object at that new priority location if you want to make further changes to it.
setStopIfTrue(stopIfTrue)	If the conditions of this conditional format are met, no lower-priority formats shall take effect on that cell. Value is null on data bars, icon sets, and color scales as there's no concept of StopIfTrue for these.

Method Details

delete()

Deletes this conditional format.

delete(): void;

Returns

void

getCellValue()

Returns the cell value conditional format properties if the current conditional format is a CellValue type.

getCellValue(): CellValueConditionalFormat | undefined;

Returns

ExcelScript.CellValueConditionalFormat | undefined

Examples

/**
 * This script applies conditional formatting to a range.
 * That formatting is conditional upon the cell's numerical value.
 * Any value between 50 and 75 will have the cell fill color changed and the font made italic.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the range to format.
  const sheet = workbook.getActiveWorksheet();
  const ratingColumn = sheet.getRange("D2:D20");

  // Add cell value conditional formatting.
  const cellValueConditionalFormatting =
    ratingColumn.addConditionalFormat(ExcelScript.ConditionalFormatType.cellValue).getCellValue();
  
  // Create the condition, in this case when the cell value is between 50 and 75.
  let rule: ExcelScript.ConditionalCellValueRule = {
    formula1: "50",
    formula2: "75",
    operator: ExcelScript.ConditionalCellValueOperator.between
  };
  cellValueConditionalFormatting.setRule(rule);

  // Set the format to apply when the condition is met.
  let format = cellValueConditionalFormatting.getFormat();
  format.getFill().setColor("yellow");
  format.getFont().setItalic(true);

}

getColorScale()

Returns the color scale conditional format properties if the current conditional format is a ColorScale type.

getColorScale(): ColorScaleConditionalFormat | undefined;

Returns

ExcelScript.ColorScaleConditionalFormat | undefined

Examples

/**
 * This script applies a red, white, and blue color scale to the selected range.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the selected range.
  let selectedRange = workbook.getSelectedRange();

  // Create a new conditional formatting object by adding one to the range.
  let conditionalFormatting = selectedRange.addConditionalFormat(ExcelScript.ConditionalFormatType.colorScale);

  // Set the colors for the three parts of the scale: minimum, midpoint, and maximum.
  conditionalFormatting.getColorScale().setCriteria({
    minimum: {
      color:"#F8696B", /* A pale red. */
      type:ExcelScript.ConditionalFormatColorCriterionType.lowestValue
    },
    midpoint: {
      color: "#FCFCFF", /* Slightly off-white. */
      formula:'=50',type:ExcelScript.ConditionalFormatColorCriterionType.percentile
    },
    maximum: {
      color: "#5A8AC6", /* A pale blue. */
      type:ExcelScript.ConditionalFormatColorCriterionType.highestValue
    }
  });
}

getCustom()

Returns the custom conditional format properties if the current conditional format is a custom type.

getCustom(): CustomConditionalFormat | undefined;

Returns

ExcelScript.CustomConditionalFormat | undefined

Examples

/**
 * This script applies a custom conditional formatting to the selected range.
 * A light-green fill is applied to a cell if the value is larger than the value in the row's previous column.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the selected cells.
  let selectedRange = workbook.getSelectedRange();

  // Apply a rule for positive change from the previous column.
  let positiveChange = selectedRange.addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
  positiveChange.getCustom().getFormat().getFill().setColor("lightgreen");
  positiveChange.getCustom().getRule().setFormula(`=${selectedRange.getCell(0, 0).getAddress()}>${selectedRange.getOffsetRange(0, -1).getCell(0, 0).getAddress()}`);
}

getDataBar()

Returns the data bar properties if the current conditional format is a data bar.

getDataBar(): DataBarConditionalFormat | undefined;

Returns

ExcelScript.DataBarConditionalFormat | undefined

Examples

/**
 * This script creates data bar conditional formatting on the selected range.
 * The scale of the data bar goes from 0 to 1000.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the selected range.
  const selected = workbook.getSelectedRange();
  
  // Create new conditional formatting on the range.
  const format = selected.addConditionalFormat(ExcelScript.ConditionalFormatType.dataBar);
  const dataBarFormat = format.getDataBar();

  // Set the lower bound of the data bar formatting to be 0.
  const lowerBound: ExcelScript.ConditionalDataBarRule = {
    type: ExcelScript.ConditionalFormatRuleType.number,
    formula: "0"
  };
  dataBarFormat.setLowerBoundRule(lowerBound);

  // Set the upper bound of the data bar formatting to be 1000.
  const upperBound: ExcelScript.ConditionalDataBarRule = {
    type: ExcelScript.ConditionalFormatRuleType.number,
    formula: "1000"
  };
  dataBarFormat.setUpperBoundRule(upperBound);
}

getIconSet()

Returns the icon set conditional format properties if the current conditional format is an IconSet type.

getIconSet(): IconSetConditionalFormat | undefined;

Returns

ExcelScript.IconSetConditionalFormat | undefined

Examples

/**
 * This script applies icon set conditional formatting to a range.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the range "A1:A5" on the current worksheet.
  const sheet = workbook.getActiveWorksheet();
  const range = sheet.getRange("A1:A5");

  // Create icon set conditional formatting on the range.
  const conditionalFormatting = range.addConditionalFormat(ExcelScript.ConditionalFormatType.iconSet);

  // Use the "3 Traffic Lights (Unrimmed)" set.
  conditionalFormatting.getIconSet().setStyle(ExcelScript.IconSet.threeTrafficLights1);

  // Set the criteria to use a different icon for the bottom, middle, and top thirds of the values in the range.
  conditionalFormatting.getIconSet().setCriteria([
    {
      formula:'=0',operator:ExcelScript.ConditionalIconCriterionOperator.greaterThanOrEqual,
      type:ExcelScript.ConditionalFormatIconRuleType.percent
    },
    {
      formula:'=33',operator:ExcelScript.ConditionalIconCriterionOperator.greaterThanOrEqual,
      type:ExcelScript.ConditionalFormatIconRuleType.percent},
    {
      formula:'=67',operator:ExcelScript.ConditionalIconCriterionOperator.greaterThanOrEqual,
      type:ExcelScript.ConditionalFormatIconRuleType.percent
    }]);
}

getId()

The priority of the conditional format in the current ConditionalFormatCollection.

getId(): string;

Returns

string

getPreset()

Returns the preset criteria conditional format. See ExcelScript.PresetCriteriaConditionalFormat for more details.

getPreset(): PresetCriteriaConditionalFormat | undefined;

Returns

ExcelScript.PresetCriteriaConditionalFormat | undefined

Examples

/**
 * This script applies a conditional format that uses a preset criterion.
 * Any cell in row 1 will have the color fill set to green if it is a duplicate value
 * (of anything else in row 1).
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the range for row 1.
  const sheet = workbook.getActiveWorksheet();
  const formattedRange = sheet.getRange("1:1");

  // Add new conditional formatting to that range.
  const conditionalFormat = formattedRange.addConditionalFormat(
    ExcelScript.ConditionalFormatType.presetCriteria);

  // Set the conditional formatting to apply a green fill.
  const presetFormat = conditionalFormat.getPreset();
  presetFormat.getFormat().getFill().setColor("green");

  // Set a rule to apply the conditional format when values are duplicated in the range.
  const duplicateRule: ExcelScript.ConditionalPresetCriteriaRule = {
    criterion: ExcelScript.ConditionalFormatPresetCriterion.duplicateValues
  };
  presetFormat.setRule(duplicateRule);
}

getPriority()

The priority (or index) within the conditional format collection that this conditional format currently exists in. Changing this also changes other conditional formats' priorities, to allow for a contiguous priority order. Use a negative priority to begin from the back. Priorities greater than the bounds will get and set to the maximum (or minimum if negative) priority. Also note that if you change the priority, you have to re-fetch a new copy of the object at that new priority location if you want to make further changes to it.

getPriority(): number;

Returns

number

getRange()

Returns the range to which the conditional format is applied. If the conditional format is applied to multiple ranges, then this method returns undefined.

getRange(): Range;

Returns

ExcelScript.Range

getRanges()

Returns the RangeAreas, comprising one or more rectangular ranges, to which the conditional format is applied.

getRanges(): RangeAreas;

Returns

ExcelScript.RangeAreas

getStopIfTrue()

If the conditions of this conditional format are met, no lower-priority formats shall take effect on that cell. Value is null on data bars, icon sets, and color scales as there's no concept of StopIfTrue for these.

getStopIfTrue(): boolean;

Returns

boolean

getTextComparison()

Returns the specific text conditional format properties if the current conditional format is a text type. For example, to format cells matching the word "Text".

getTextComparison(): TextConditionalFormat | undefined;

Returns

ExcelScript.TextConditionalFormat | undefined

Examples

/**
 * This script adds conditional formatting to the first column in the worksheet.
 * This formatting gives the cells a green fill if they have text starting with "Excel".
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first column in the current worksheet.
  const currentSheet = workbook.getActiveWorksheet();
  const firstColumn = currentSheet.getRange("A:A");

  // Add conditional formatting based on the text in the cells.
  const textConditionFormat = 
    firstColumn.addConditionalFormat(ExcelScript.ConditionalFormatType.containsText).getTextComparison();

  // Set the conditional format to provide a green fill.
  textConditionFormat.getFormat().getFill().setColor("green");

  // Apply the condition rule that the text begins with "Excel".
  const textRule: ExcelScript.ConditionalTextComparisonRule = {
    operator: ExcelScript.ConditionalTextOperator.beginsWith,
    text: "Excel"
  };
  textConditionFormat.setRule(textRule);
}

getTopBottom()

Returns the top/bottom conditional format properties if the current conditional format is a TopBottom type. For example, to format the top 10% or bottom 10 items.

getTopBottom(): TopBottomConditionalFormat | undefined;

Returns

ExcelScript.TopBottomConditionalFormat | undefined

Examples

/**
 * This script applies top/bottom conditional formatting to a range.
 * The top 2 values in the range will have the cell fill color changed to green.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the range to format.
  const sheet = workbook.getWorksheet("TopBottom");
  const dataRange = sheet.getRange("B2:D5");

  // Set the fill color to green for the top 2 values in the range.
  const topBottomFormat = dataRange.addConditionalFormat(
    ExcelScript.ConditionalFormatType.topBottom).getTopBottom();
  topBottomFormat.getFormat().getFill().setColor("green");
  topBottomFormat.setRule({
    rank: 2, /* The numeric threshold. */
    type: ExcelScript.ConditionalTopBottomCriterionType.topItems /* The type of the top/bottom condition. */
  });
}

getType()

A type of conditional format. Only one can be set at a time.

getType(): ConditionalFormatType;

Returns

ExcelScript.ConditionalFormatType

setPriority(priority)

The priority (or index) within the conditional format collection that this conditional format currently exists in. Changing this also changes other conditional formats' priorities, to allow for a contiguous priority order. Use a negative priority to begin from the back. Priorities greater than the bounds will get and set to the maximum (or minimum if negative) priority. Also note that if you change the priority, you have to re-fetch a new copy of the object at that new priority location if you want to make further changes to it.

setPriority(priority: number): void;

Parameters

priority

number

Returns

void

setStopIfTrue(stopIfTrue)

If the conditions of this conditional format are met, no lower-priority formats shall take effect on that cell. Value is null on data bars, icon sets, and color scales as there's no concept of StopIfTrue for these.

setStopIfTrue(stopIfTrue: boolean): void;

Parameters

stopIfTrue

boolean

Returns

void