Conditional Formatting

Conditional Formatting is the automatic formatting of cells based on the values that the cells contain.
You can define conditions which when true will mean formatting is applied to the cells.
If the condition is False, then no formatting is applied.
This is very useful for quickly identifying cells that meet certain criteria.
For example conditional formatting could be used to quickly identify all the cells which contain a value greater than 10.

What can you use Conditional Formatting for ?

This formatting is dynamic and is done automatically as the value changes.
Here are just some of the uses which conditional formatting can be used for.
1) Identifying all negative numbers in a range of cells.
2) Identifying all cells that contain a value in a specific range.
3) Identifying all the cells that contain an error.
4) Quickly shading alternate rows or columns.

Cells must be numbers and not text for the conditions to work !!
Left(sText,1) etc will not be recognised and the conditions will not work - This is a very common mistake.

Using Conditional Formatting

This can be found by selecting (Format > Conditional Formatting).
You can enter a maximum of three conditions per cell
The condition can either be based on the value in that particular cell or based on a formula.
Use the first drop-down list to select the type of condition you would like to use.
You can create a condition based either on the actual value in the cell or a formula that uses that cell to determine a condition.

Cell Value is

This is for simple conditions, for example:

Formula is

This is for more complicated conditions, for example:

The Order Matters

If Excel encounters cells that meet more than one conditional formatting condition it only applies the first one.
Lets assume you want all values over 300 to appear in blue and any values between 200 and 400 to appear in green.
For this you would need to create two conditional format statements.
The precise formatting will depend on the order in which you define them.
If you define > 300 = blue and 200 < < 400 = green
and the values 300 to 399 will be in blue rather than green, since the first condition is true.

If you define 200 < < 400 = green and > 300 = blue
you get the correct formatting.

Applying to non Contiguous Ranges

The range selection to be conditionally formatting can be non-contiguous. In order to make multiple selections, press Shift and F8 after making the first selection. Then highlight the other range(s) to be added.

Referring to other worksheets

It is possible to refer to other worksheets when you are using cell references in your conditions.
This can be achieved in two ways:
1) Create a named range for the cell (or range of cells) you want to use
2) Use a cell reference on the active worksheet that refers to the other worksheet.

Important

You can only have a maximum of 64 conditions in 2007. The limit in 2003 is 3.
If you want to apply conditional formatting to a large block of cells, it is easier to apply the formatting to just one cell and then copy and paste the format.
The conditional formatting will get copied automatically to any new rows or columns that get inserted into a range of cells that contains conditional formatting.
Try to avoid applying conditional formatting to an entire column or row as this will increase the size of your workbook. Only apply it to the necessary range.
When 2 rules conflict, the last rule you set wins. The newest rule takes precedence and will appear first in the Rules Manager dialog box.

Adding Custom Conditional Formatting to Excel 2007 - docs.microsoft.com/en-us/previous-versions/office/developer/officetalk2007/bb226688(v=office.11)
Adding Customized Color Scales to Excel 2007 - docs.microsoft.com/en-us/previous-versions/office/developer/officetalk2007/bb428945(v=office.11)
Adding Customized Data Bars to Excel 2007 - docs.microsoft.com/en-us/previous-versions/office/developer/officetalk2007/bb229709(v=office.11)
Adding Customized Rules to Excel 2007 - docs.microsoft.com/en-us/previous-versions/office/developer/officetalk2007/bb286672(v=office.11)
Adding Customized Icon Sets to Excel 2007 - docs.microsoft.com/en-us/previous-versions/office/developer/officetalk2007/bb407306(v=office.11)