Home Index Book Sheet Format Font AutoFilter FilterColumn RichString FormControl
ConditionalFormatting ConditionalFormat Examples
ConditionalFormatting ConditionalFormat Examples
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, const wchar_t* value = 0, 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.
Home Index Book Sheet Format Font AutoFilter FilterColumn RichString FormControl ConditionalFormatting ConditionalFormat Examples