Conditional cell formatting

setFormatIf(reference, action, if_condition, format_true, format_false)

Formats a given cell or a range of cells. The numeric action parameter specifies which formatting attributes to set or clear (restore defaults).

Usually it's convenient to keep all the formatting funtions in a separate worksheet. A single cell selection/value can control the appearances of other worksheets.

The function can be applied both to existing cells and empty regions without any previous formatting (and in this scenario the 1st update can be slightly slower).
There are no limitations for the number or complexity concerning this function. You can use millions of conditionally formatted cells or regions same as any other formulas and nested IFs.

If the reference argument specifies entire columns or rows, the formatting action will be applied respectively to column and row styles. The formatting layers are as follows: a table style < column styles < row styles < cell styles (and optionally colors defined in custom style patterns). Cell styles overwrite all other styles.

The format_true and format_false parameters be -1 to restore the default cell state for a given attribute or the following:

The 32nd action type sets all possible cell formatting/style attributes at once. Custom cell styles are added to a workbook with the Format > Custom Cell Styles dialog box and are saved in a workbook. Custom cell styles in a workbook can be saved to or loaded from a global GS-Calc cache using the Save As Default App Styles and Load Default App Styles commands in the Custom Cell Styles dialog box.

For the action = 20:

The position attribute can a space-separated list of the following names:

The width attribute can be:

The style attribute can be:

The color attribute can a numeric RGB value, a string representing the RGB triple e.g. "#FF0000" (which is red) or one of the predefined color names:

The function returns the evaluated value of the if_condition parameter.

The "Custom Cell Styles" dialog box:

The "Custom Cell Styles" dialog box and options:


setFormatIf(c120, 11, c120 > 0, "green", "red")

setFormatIf(c:e, 11, c120 > 0, "green", "red")

setFormatIf(10:20, 11, c120 > 0, "green", "red")

setFormatIf(c120, 20, c120 > 0, "all 2px solid green", "diag-left diag-right red")

setFormatIf(d100:d999, 1, c1="use format", "currency", 0)

setFormatIf(d99, 20, a1, "bottom 2px dotted green", -1)

setFormatIf(d99, 32, a10 > 10, "my-style-1", "my-style-2")