Leading the way in Microsoft Office Development

Including a 4th Condition

Before you apply the conditional formatting to your cells format all your cells with the 4th condition formatting.

For example this will allow you to have four different types of shading automatically applied to your cells.

All the cells in the range where initially formatted with the pink colour to represent numbers from 0 to 25.

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.

Removing Conditional Formatting

Pressing the Delete key to remove the contents from a cell will not remove the conditional formatting.

This is in the same way that pressing the Delete key does not remove the number format.

You press (Edit > Clear > Formats) to remove the conditional formatting.

A quick way to remove the conditional formatting is to copy a blank cell over the top.

Formatting your cells

You can change the Font, Border and Pattern of a cell.

You cannot change the Font name or font size.

Changing the font size has the potential to change the height of the rows which could create problems so this is not done automatically.

You can only choose from the 56 colours on the colour palette which is specific to that workbook.

If you want to use a different colour then you must change the colour palette using (Tools > Options).

Finding cells that have conditional formatting

Use the (Edit > Goto) dialog box and select "Special".

Selecting "All" will highlight all the cells on the active worksheet that contain conditional formatting.

Selecting "Same" will only highlight cells on the active worksheet which have the same conditional formatting as the active cell.

In Excel 2002 and Excel 2003 you can use the (Edit > Find) dialog box to locate cells with special formatting.

However this will not identify any cells that have been formatted as a consequence of conditional formatting.

Shading Alternate Rows and Columns

You can use Conditional Formatting to apply alternate shading to your rows and columns.

For more details please refer to the Formatting > Shading Rows and Columns page.

Sorting cells based on conditional formatting

You cannot currently sort a cell range based on the conditional formatting, however an alternative is to insert the same condition in the corresponding row or column, so it returns True or False. You can then sort by this row or column.

SS

You can use conditional formatting as a way to check that the data is sorted correctly.

Have 2 conditions A2 A1

Toggle your Conditional Formatting

Add a checkbox from the Forms toolbar and have the result in a cell which is used by the conditional formatting.

AND(------< CheckBoxResult)

Pasting data on top of cells that contain conditional formatting will delete the conditional formatting.

Conditional formatting is treated very much like a number format.

Copying a cell that contains conditional formatting will also copy the conditional formatting.