The conditional formatting feature allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula. The Expression property specifies a formula that indicates the criteria to format the items or the columns. Use the ApplyTo property to specify when the items or the columns are  formatted. Use the Add method to specify the expression at adding time. The Expression property may include variables, constants, operators or ( ) parenthesis.  A variable is defined as %n, where n is the index of the column ( zero based ). For instance, the %0 indicates the first column, the %1, indicates the second column, and so on.  The Valid property specifies whether the expression is syntactically correct, and can be evaluated. If the expression contains a variable that is not known, 0 ( or empty string ) value is used instead. For instance, if your control has 2 columns, and the expression looks like "%2 +%1 ", the %2 does not exist, 0 is used instead. For instance, if the control contains two columns the known variables are %0 and %1. 

The expression may be a combination of variables, constants, strings, dates and operators.  A string is delimited by ", ` or ' characters, and inside they can have the starting character preceded by \ character, ie "\"This is a quote\"". A date is delimited by two # characters, ie #1/31/2001 10:00# means the January 31th, 2001, 10:00 AM.

The supported binary arithmetic operators are:

• * ( multiplicity operator ), priority 5
• / ( divide operator ), priority 5
• + ( addition operator ), priority 4 ( concatenates two strings, if one of the operands is of string type )
• - ( subtraction operator ), priority 4

The supported unary boolean operators are:

• not ( not operator ), priority 3 ( high priority )

The supported binary boolean operators are:

• or ( or operator ), priority 2
• and ( or operator ), priority 1

The supported binary boolean operators, all these with the same priority 0, are :

• < ( less operator )
• <= ( less or equal operator )
• = ( equal operator )
• != ( not equal operator )
• >= ( greater or equal operator )
• > ( greater operator )

Obviously, the priority of the operations inside the expression is determined by ( ) parenthesis and the priority for each operator. 

The supported conversion unary operators are:

• type (unary operator) retrieves the type of the object. For instance type(%0) = 8 specifies the cells that contains string values. 

  Here's few predefined types:

   

  • 0 - empty ( not initialized )
  • 1 - null
  • 2 - short
  • 3 - long
  • 4 - float
  • 5 - double
  • 6 - currency
  • 7 - date
  • 8 - string
  • 9 - object
  • 10 - error
  • 11 - boolean
  • 12 - variant
  • 13 - any
  • 14 - decimal
  • 16 - char
  • 17 - byte
  • 18 - unsigned short 
  • 19 - unsigned long
  • 20 - long on 64 bits 
  • 21 - unsigned long on 64 bites

   

• str (unary operator) converts the expression to a string
• dbl (unary operator) converts the expression to a number
• date (unary operator) converts the expression to a date

Other known operators for numbers are:

• int (unary operator) retrieves the integer part of the number
• round (unary operator) rounds the number ie 1.2 gets 1, since 1.8 gets 2
• floor (unary operator) returns the largest number with no fraction part that is not greater than the value of its argument
• abs (unary operator) retrieves the absolute part of the number ie -1 gets 1, 2 gets 2

Other known operators for strings are:

• len (unary operator) retrieves the number of characters in the string
• lower (unary operator) returns a string expression in lowercase letters
• upper (unary operator) returns a string expression in uppercase letters
• proper (unary operator) returns from a character expression a string capitalized as appropriate for proper names
• ltrim (unary operator) removes spaces on the left side of a string
• rtrim (unary operator) removes spaces on the right side of a string
• trim (unary operator) removes spaces on both sides of a string
• startwith (binary operator) specifies whether a string starts with specified string
• endwith (binary operator) specifies whether a string ends with specified string
• contains (binary operator) specifies whether a string contains another specified string
• left (binary operator) retrieves the left part of the string
• right (binary operator) retrieves the right part of the string
• a mid b (binary operator) retrieves the middle part of the string a starting from b ( 1 means first position, and so on )
• a count b (binary operator) retrieves the number of occurrences of the b in a
• a replace b with c (double binary operator) replaces in a the b with c, and gets the result.

Other known operators for dates are:

• time (unary operator) retrieves the time of the date
• shortdate (unary operator) formats a date as a date string using the short date format
• longdate (unary operator) formats a date as a date string using the long date format
• year (unary operator) retrieves the year of the date
• month (unary operator) retrieves the month of the date ( 1, 2,...,12 )
• day (unary operator) retrieves the day of the date ( 1, 2,...,31 )
• yearday (unary operator) retrieves the number of the day in the year, or the days since January 1st ( 0, 1,...,365 )
• weekday (unary operator) retrieves the number of days since Sunday ( 0 - Sunday, 1 - Monday,..., 6 - Saturday )
• hour (unary operator) retrieves the hour of the date ( 0, 1, ..., 23 )
• min (unary operator) retrieves the minute of the date ( 0, 1, ..., 59 )
• sec (unary operator) retrieves the second of the date ( 0, 1, ..., 59 )

The expression supports also immediate if ( similar with iif in visual basic, or ? : in C++ ) ie cond ? value_true : value_false, which means that once that cond is true the value_true is used, else the value_false is used. Also, it supports variables, up to 10 from 0 to 9. For instance, 0:="Abc" means that in the variable 0 is "Abc", and =:0 means retrieves the value of the variable 0. For instance, the "len(%0) ? ( 0:=(%1+%2) ? currency(=:0) else `` ) : ``" gets the sum between second and third column in currency format if it is not zero, and only if the first column is not empty. As you can see you can use the variables to avoid computing several times the same thing ( in this case the sum %1 and %2 .

Samples:

1. "1", highlights all cells or items. Use this form, when you need to highlight all cells or items in the column or control. 
2. "%0 >= 0", highlights the cells or items, when the cells in the first column have the value greater or equal with zero
3. "%0 = 1 and %1 = 0", highlights the cells or items, when the cells in the first column have the value equal with 0, and the cells in the second column have the value equal with 0 
4. "%0+%1>%2", highlights the cells or the items, when the sum between first two columns is greater than the value in the third column
5. "%0+%1 > %2+%3", highlights the cells or items, when the sum between first two columns is greater than the sum between third and forth column.
6. "%0+%1 >= 0 and (%2+%3)/2 < %4-5", highlights the cells or the items, when the sum between first two columns is greater than 0 and the half of the sum between third and forth columns is less than fifth column minus 5.
7. "%0 startwith 'A'" specifies the cells that starts with A
8. "%0 endwith 'Bc'" specifies the cells that ends with Bc
9. "%0 contains 'aBc'" specifies the cells that contains the aBc string
10. "lower(%0) contains 'abc'" specifies the cells that contains the abc, AbC, ABC, and so on
11. "upper(%0)'" retrieves the uppercase string
12. "len(%0)>0'" specifies the not blanks cells
13. "len %0 = 0'" specifies the blanks cells

The conditional format feature may change the cells and items as follows:

• Bold property. Bolds the cell or items
• Italic property. Indicates whether the cells or items should appear in italic.
• StrikeOut property. Indicates whether the cells or items should appear in strikeout.
• Underline property. Underlines the cells or items
• Font property. Changes the font for cells or items.
• BackColor property. Changes the background color for cells or items, supports skins as well.
• ForeColor property. Changes the foreground color for cells or items.

The following VB samples bolds all items when the sum between first two columns is greater than 0:

List1.ConditionalFormats.Add("%0+%1>0").Bold = True

The following C++ sample bolds all items when the sum between first two columns is greater than 0:

COleVariant vtEmpty;
m_List.GetConditionalFormats().Add( "%0+%1>0", vtEmpty ).SetBold( TRUE );

The following VB.NET sample bolds all items when the sum between first two columns is greater than 0:

AxList1.ConditionalFormats.Add("%0+%1>0").Bold = True

The following C# sample bolds all items when the sum between first two columns is greater than 0:

axList1.ConditionalFormats.Add("%0+%1>0", null).Bold = true

The following VFP sample bolds all items when the sum between first two columns is greater than 0:

thisform.List1.ConditionalFormats.Add("%0+%1>0").Bold = .t.

 

---

Send comments on this topic.

© 1999-2011 Exontrol.COM, Software. All rights reserved.