Conditional Rule macro checks the conditions for applying formatting options to conformant elements. This macro must be used inside Conditional Rules Manager macro.

Conditional Rule macro has the following parameters:

Parameter Description
[Enum] Rule condition (default=”Element value is equal to”) The type of condition to be checked. Based on this type Value1, Value 1 + Value 2 or none of them may have to be specified. May have one of the following values:
  • Element value is between
    • Applies formatting when the cell numeric value is in the range between Value1 and Value2 parameter values inclusively
    • If cell value or Value1 or Value2 is not numerical (i.e. neither Long nor Double), the rule is not checked
    • The range is taken between minimal of Value1 and Value2 and maximum of Value1 and Value2
  • Element value is not between
    • Applies formatting when the cell numeric value is not in the range between Value1 and Value2 parameter values exclusively
    • If cell value or Value1 or Value2 is not numerical (i.e. neither Long nor Double), the rule is not checked
    • The range is taken between minimal of Value1 and Value2 and maximum of Value1 and Value2
  • Element value is equal to
    • Applies formatting when the cell value is equal to the Value1 parameter value
    • If cell value and Value1 are both numeric values, they are compared numerically, otherwise they are compared as Text (case insensitive)
    • The value of Value2 parameter is ignored
  • Element value is not equal to
    • Applies formatting when the cell value is not equal to the Value1 parameter value
    • If cell value and Value1 are both numeric values, they are compared numerically, otherwise they are compared as Text (case insensitive)
    • The value of Value2 parameter is ignored
  • Element value is greater than
    • Applies formatting when the numerical cell value is greater than the Value1 parameter numerical value
    • If cell value or Value1 is not numerical, the rule is not checked
    • The value of Value2 parameter is ignored
  • Element value is less than
    • Applies formatting when the numerical cell value is less than the Value1 parameter numerical value
    • If cell value or Value1 is not numerical, the rule is not checked
    • The value of Value2 parameter is ignored
  • Element value is greater than or equal to
    • Applies formatting when the numerical cell value is greater than or equal to the Value1 parameter numerical value
    • If cell value or Value1 is not numerical, the rule is not checked
    • The value of Value2 parameter is ignored
  • Element value is less than or equal to
    • Applies formatting when the numerical cell value is less than or equal to the Value1 parameter numerical value
    • If cell value or Value1 is not numerical, the rule is not checked
    • The value of Value2 parameter is ignored
  • Element text contains
    • Applies formatting when the cell contains the Value1 parameter text
    • The search is case insensitive
    • The value of Value2 parameter is ignored
  • Element text does not contain
    • Applies formatting when the cell does not contain the Value1 parameter text
    • The search is case insensitive
    • The value of Value2 parameter is ignored
  • Element text begins with
    • Applies formatting when the cell begins with the Value1 parameter text
    • The search is case insensitive
    • The value of Value2 parameter is ignored
  • Element text ends with
    • Applies formatting when the cell ends with the Value1 parameter text
    • The search is case insensitive
    • The value of Value2 parameter is ignored
  • Element is blank
    • Applies formatting when the cell is blank
    • The check is done by measuring cell text length – if it is zero, then the cell is blank
    • The values of Value1 and Value2 parameters are ignored
  • Element is not blank
    • Applies formatting when the cell is not blank
    • The check is done by measuring cell text length – if it is greater than zero, then the cell is not blank
    • The values of Value1 and Value2 parameters are ignored
  • Element is a numeric value
    • Applies formatting when the cell is a numerical value
    • The check is done by checking for Long and Double types
    • The values of Value1 and Value2 parameters are ignored
  • Formula result value is true
    • Applies formatting when the result of the formula, specified in Value1 parameter, is true
    • Any applicable formula from the list of CelesteCS Math add-on formulas may be used
    • The table cells may be referenced as A1, $A1, A$1, $A$1
    • The table ranges may be specified as $A$1:$B$2
    • The value of Value2 parameter is ignored
  • Element value is above average
    • Applies formatting when the cell’s numeric value is greater than the average of all numeric values in the evaluated range
    • If the cell value is not numerical, the rule is not checked
    • The values of Value1 and Value2 parameters are ignored
  • Element value is below average
    • Applies formatting when the cell’s numeric value is less than the average of all numeric values in the evaluated range
    • If the cell value is not numerical, the rule is not checked
    • The values of Value1 and Value2 parameters are ignored
  • Element value is in top N
    • Applies formatting when the cell’s numeric value is among the N largest values in the evaluated range
    • Value1 specifies N, and must be a whole number
    • Ties at the threshold may result in more than N cells matching, the same way Excel’s own Top N rule works
    • The value of Value2 parameter is ignored
  • Element value is in bottom N
    • Applies formatting when the cell’s numeric value is among the N smallest values in the evaluated range
    • Value1 specifies N, and must be a whole number
    • Ties at the threshold may result in more than N cells matching, the same way Excel’s own Bottom N rule works
    • The value of Value2 parameter is ignored
  • Element value is a duplicate
    • Applies formatting when the cell’s text value (trimmed, case insensitive) occurs more than once in the evaluated range
    • The values of Value1 and Value2 parameters are ignored
  • Element value is unique
    • Applies formatting when the cell’s text value (trimmed, case insensitive) occurs exactly once in the evaluated range
    • The values of Value1 and Value2 parameters are ignored
  • Element date is before
    • Applies formatting when the cell’s date value is before the date specified in Value1
    • Value1 must be either the literal word “Today” or a date in ISO-8601 format (YYYY-MM-DD)
    • The cell value itself must also be in ISO-8601 format (YYYY-MM-DD) to be recognized as a date
    • The value of Value2 parameter is ignored
  • Element date is after
    • Applies formatting when the cell’s date value is after the date specified in Value1
    • Value1 must be either the literal word “Today” or a date in ISO-8601 format (YYYY-MM-DD)
    • The cell value itself must also be in ISO-8601 format (YYYY-MM-DD) to be recognized as a date
    • The value of Value2 parameter is ignored
  • Element date is today
    • Applies formatting when the cell’s date value is equal to today’s date
    • The cell value must be in ISO-8601 format (YYYY-MM-DD) to be recognized as a date
    • The values of Value1 and Value2 parameters are ignored
  • Element date is overdue
    • Applies formatting when the cell’s date value is before today’s date
    • The cell value must be in ISO-8601 format (YYYY-MM-DD) to be recognized as a date
    • The values of Value1 and Value2 parameters are ignored
  • Element date is in the next N days
    • Applies formatting when the cell’s date value is between today and N days from today, inclusive
    • Value1 specifies N, and must be a whole number
    • The cell value must be in ISO-8601 format (YYYY-MM-DD) to be recognized as a date
    • The value of Value2 parameter is ignored
  • Element date is in the last N days
    • Applies formatting when the cell’s date value is between N days before today and today, inclusive
    • Value1 specifies N, and must be a whole number
    • The cell value must be in ISO-8601 format (YYYY-MM-DD) to be recognized as a date
    • The value of Value2 parameter is ignored
  • Element date is yesterday
    • Applies formatting when the cell’s date value is equal to yesterday’s date
    • The cell value must be in ISO-8601 format (YYYY-MM-DD) to be recognized as a date
    • The values of Value1 and Value2 parameters are ignored
  • Element text length is greater than
    • Applies formatting when the length of the cell’s text is greater than the number specified in Value1
    • Value1 must be a whole number
    • The value of Value2 parameter is ignored
  • Element text length is less than
    • Applies formatting when the length of the cell’s text is less than the number specified in Value1
    • Value1 must be a whole number
    • The value of Value2 parameter is ignored
[String] Value 1 (default=empty) The first parameter of the condition (where applied).
[String] Value 2 (default=empty) The second parameter of the condition (where applied).
[String] Applies to (default='All tables') Specifies the cells to apply conditional formatting to.
  • Set to 'All tables' to apply formatting to all cells of all tables.
  • Set to 'Whole text' to apply formatting to whole macro body text. Read more here.
  • An exact column range may be specified to check conditions on the specified columns only. Note, that exact Table specificator should also be specified. Example: 'Table1.A:B'.
  • Several ranges may be specified, separated with ';' symbol. Example: 'Table1.A:B; Table1.D:E'.
[String] Affected elements (default='Self') Specifies the cells to apply conditional formatting to. Examples: 'A:B', '2:3'.
  • Set to 'Self' to apply formatting to the cell itself.
  • An exact column or row range may be specified to apply formatting to the specified columns or rows only. Note, that Table specificator should not be specified. Examples: 'A:B', '2:3'.
  • Several ranges may NOT be specified.
[Boolean] Stop if true (default=false) Specifies whether to stop consequent rules processing over the conformant element.
[Enum] Bold text (default=”Keep original”) Specifies, whether to apply bold style to the conformant element's text. If this parameter is set to “Keep original”, the bold style will not be changed. May have one of the following values:
  • Keep original
  • Apply style
  • Remove style
[Enum] Italic text (default=”Keep original”) Specifies, whether to apply italic style to the conformant element's text. If this parameter is set to “Keep original”, the italic style will not be changed. May have one of the following values:
  • Keep original
  • Apply style
  • Remove style
[Enum] Underline text (default=”Keep original”) Specifies, whether to apply underline style to the conformant element's text. If this parameter is set to “Keep original”, the underline style will not be changed. May have one of the following values:
  • Keep original
  • Apply style
  • Remove style
[String] Text color (default=”Keep original”) Specifies whether to apply a specific color to the conformant element's text. Set to "Keep original" to keep the original color. May have one of the following values:
  • Keep original
  • CSS colors
    • aquamarine
    • green
    • and so on…
  • Hex colors
    • #FFCCCC
    • #555555
    • and so on…
[String] Background color (default=”Keep original”) Specifies, whether to apply a specific background color to the conformant element. Set to "Keep original" to keep the original color. May have one of the following values:
  • Keep original
  • CSS colors
    • aquamarine
    • green
    • and so on…
  • Hex colors
    • #FFCCCC
    • #555555
    • and so on…
  • Confluence table background colors
    • highlight-grey
    • highlight-red
    • highlight-green
    • highlight-blue
    • highlight-yellow
[String] Replacement text (default=”Keep original”) Specifies the text to replace the text of the conformant element. Set to "Keep original" to keep the original text. May have one of the following values:
  • Keep original
  • Any text to replace the text of conformant element with
[Enum] Insert status color (default=”None”)
New! Datacenter Only		 Inserts a coloured Confluence-style status lozenge instead of the conformant element’s text, replacing whatever Replacement text would otherwise do. Set to “None” to disable and use Replacement text normally. May have one of the following values:
  • None
  • Grey
  • Red
  • Yellow
  • Green
  • Blue
  • Purple
[String] Insert status title (default=empty)
New! Datacenter Only		 The text shown inside the status lozenge. If left empty, the conformant element’s own original value is used as the title. Only used when Insert status color is not “None”.

When the parameters are set, an example of specified formatting application may be seen at the preview pane of the macro editor. Press “Preview” button after changing formatting or replacement parameters to see the result.