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 valueDescription
    CFORMAT_BEGINWITHhighlights cells that begin with the given text
    CFORMAT_CONTAINSBLANKShighlights cells that are completely blank
    CFORMAT_CONTAINSERRORShighlights cells with formula errors
    CFORMAT_CONTAINSTEXThighlights cells containing given text
    CFORMAT_DUPLICATEVALUEShighlights duplicated values
    CFORMAT_ENDSWITHhighlights cells ending with given text
    CFORMAT_EXPRESSIONhighlights cells when the formula result is true
    CFORMAT_NOTCONTAINSBLANKShighlights cells that are not blank
    CFORMAT_NOTCONTAINSERRORShighlights cells without formula errors
    CFORMAT_NOTCONTAINSTEXThighlights cells that do not contain given text
    CFORMAT_UNIQUEVALUEShighlights 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 valueDescription
    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 valueDescription
    CFTP_LAST7DAYSa date in the last seven days
    CFTP_LASTMONTHa date occuring in the last calendar month
    CFTP_LASTWEEKa date occuring last week
    CFTP_NEXTMONTHa date occuring in the next calendar month
    CFTP_NEXTWEEKa date occuring next week
    CFTP_THISMONTHa date occuring in this calendar month
    CFTP_THISWEEKa date occuring this week
    CFTP_TODAYtoday's date
    CFTP_TOMORROWtomorrow's date
    CFTP_YESTERDAYyesterday'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 valueDescription
    CFVO_MINindicates that the minimum value in the range shall be used as the minimum value for the gradient
    CFVO_MAXindicates that the maximum value in the range shall be used as the maximum value for the gradient
    CFVO_FORMULAthe minimum/midpoint/maximum value for the gradient is determined by a formula
    CFVO_NUMBERindicates that the minimum/midpoint/maximum value for the gradient is specified by a constant numeric value
    CFVO_PERCENTvalue 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_PERCENTILEvalue 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.