• Home
• Download
• Documentation
• Examples
• Setup
• FAQ
• Purchase
• Contact

ConditionalFormatting class reference

• void addRange(int rowFirst, int rowLast, int colFirst, int colLast)
  Adds a range to these conditional formatting rules.
• void addRule(CFormatType type, ConditionalFormat* cFormat, const wchar_t* value = 0, bool stopIfTrue = 0)
  Adds a conditional formatting rule that highlights cells whose values correspond to the specified criteria:
  type - the conditional format rule type, see the list of types below;
  cFormat - the conditional format used for highlighting cells, use the Book::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;

  CFormatType value	Description
  CFORMAT_BEGINWITH	highlights cells that begin with the given text
  CFORMAT_CONTAINSBLANKS	highlights cells that are completely blank
  CFORMAT_CONTAINSERRORS	highlights cells with formula errors
  CFORMAT_CONTAINSTEXT	highlights cells containing given text
  CFORMAT_DUPLICATEVALUES	highlights duplicated values
  CFORMAT_ENDSWITH	highlights cells ending with given text
  CFORMAT_EXPRESSION	highlights cells when the formula result is true
  CFORMAT_NOTCONTAINSBLANKS	highlights cells that are not blank
  CFORMAT_NOTCONTAINSERRORS	highlights cells without formula errors
  CFORMAT_NOTCONTAINSTEXT	highlights cells that do not contain given text
  CFORMAT_UNIQUEVALUES	highlights unique values in the range

• void addTopRule(ConditionalFormat* cFormat, int value, bool bottom = false, bool percent = false, bool stopIfTrue = 0)
  Adds a conditional formatting rule that highlights cells whose values fall in the [top N] or [bottom N] bracket:
  cFormat - the conditional format used for highlighting cells, use the Book::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.
• void addOpNumRule(CFormatOperator op, ConditionalFormat* cFormat, double value1, double value2 = 0, bool stopIfTrue = 0)
  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 below;
  cFormat - the conditional format used for highlighting cells, use the Book::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.
  

  CFormatOperator value	Description
  CFOPERATOR_LESSTHAN	'Less than' operator
  CFOPERATOR_LESSTHANOREQUAL	'Less than or equal to' operator
  CFOPERATOR_EQUAL	'Equal to' operator
  CFOPERATOR_NOTEQUAL	'Not equal to' operator
  CFOPERATOR_GREATERTHANOREQUAL	'Greater than or equal to' operator
  CFOPERATOR_GREATERTHAN	'Greater than' operator
  CFOPERATOR_BETWEEN	'Between' operator
  CFOPERATOR_NOTBETWEEN	'Not between' operator
  CFOPERATOR_CONTAINSTEXT	'Contains' operator
  CFOPERATOR_NOTCONTAINS	'Does not contain' operator
  CFOPERATOR_BEGINSWITH	'Begins with' operator
  CFOPERATOR_ENDSWITH	'Ends with' operator

• void addOpStrRule(CFormatOperator op, ConditionalFormat* cFormat, const wchar_t* value1, const wchar_t* value2 = 0, bool stopIfTrue = 0)
  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;
  cFormat - the conditional format used for highlighting cells, use the Book::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.
  
• void addAboveAverageRule(ConditionalFormat* cFormat, bool aboveAverage = true, bool equalAverage = false, int stdDev = 0, bool stopIfTrue = 0)
  Adds a conditional formatting rule that highlights cells that are above or below the average for all values in the range:
  cFormat - the conditional format used for highlighting cells, use the Book::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.
  
• void addTimePeriodRule(ConditionalFormat* cFormat, CFormatTimePeriod timePeriod, bool stopIfTrue = 0)
  Adds a conditional formatting rule that highlights cells containing dates in the specified time period:
  cFormat - the conditional format used for highlighting cells, use the Book::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.

  CFormatTimePeriod value	Description
  CFTP_LAST7DAYS	a date in the last seven days
  CFTP_LASTMONTH	a date occuring in the last calendar month
  CFTP_LASTWEEK	a date occuring last week
  CFTP_NEXTMONTH	a date occuring in the next calendar month
  CFTP_NEXTWEEK	a date occuring next week
  CFTP_THISMONTH	a date occuring in this calendar month
  CFTP_THISWEEK	a date occuring this week
  CFTP_TODAY	today's date
  CFTP_TOMORROW	tomorrow's date
  CFTP_YESTERDAY	yesterday's date

• void add2ColorScaleRule(Color minColor, Color maxColor, CFVOType minType = CFVO_MIN, double minValue = 0, CFVOType maxType = CFVO_MAX, double maxValue = 0, bool stopIfTrue = 0)
  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 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.

  CFVOType value	Description
  CFVO_MIN	indicates that the minimum value in the range shall be used as the minimum value for the gradient
  CFVO_MAX	indicates that the maximum value in the range shall be used as the maximum value for the gradient
  CFVO_FORMULA	the minimum/midpoint/maximum value for the gradient is determined by a formula
  CFVO_NUMBER	indicates that the minimum/midpoint/maximum value for the gradient is specified by a constant numeric value
  CFVO_PERCENT	value indicates a percentage between the minimum and maximum values in the range shall be used as the minimum/midpoint/maximum value for the gradient
  CFVO_PERCENTILE	value indicates a percentile ranking in the range shall be used as the minimum/midpoint/maximum value for the gradient

• void add2ColorScaleFormulaRule(Color minColor, Color maxColor, CFVOType minType = CFVO_FORMULA, const char* minValue = 0, CFVOType maxType = CFVO_FORMULA, const wchar_t* maxValue = 0, bool stopIfTrue = 0)
  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.
  
• void add3ColorScaleRule(Color minColor, Color midColor, Color maxColor, CFVOType minType = CFVO_MIN, double minValue = 0, CFVOType midType = CFVO_PERCENTILE, double midValue = 50, CFVOType maxType = CFVO_MAX, double maxValue = 0, bool stopIfTrue = 0)
  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.
• void add3ColorScaleFormulaRule(Color minColor, Color midColor, Color maxColor, CFVOType minType = CFVO_FORMULA, const wchar_t* minValue = 0, CFVOType midType = CFVO_FORMULA, double midValue = 50, CFVOType maxType = CFVO_FORMULA, const wchar_t* maxValue = 0, bool stopIfTrue = 0)
  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.