# Visual Basic > Visual Basic FAQs >  [FAQ's: OD] How to do Conditional Formatting in MS Excel

## Siddharth Rout

*Conditional Formatting*
_
Note: This has been tested in Excel 2003_

Conditional Formatting enables you to apply cell Formatting selectively and automatically, based on the contents of the cells

For Example, you can set things up so that all negative values in a range have a light-yellow background color. When you enter or change a value in the range, Excel examines the value and evaluates the Conditional Formatting rules for the cell. If the value is negative, the background is shaded, If not, No Formatting is applied.

Conditional Formatting is very useful for quickly identifying erroneous cell entries or cell of a particular type.

*1) Specifying Conditional Formatting*

To apply Conditional Formatting to a cell or range

a) Select a cell or Range
b) Choose Format=>Conditional Formatting. Excel displays the Conditional  
Formatting dialog box as shown in picture below


c) In the first dropdown box, select either Cell Value Is (for simple Conditional Formatting) or Formula Is (for Formatting based on a formula).
d) Specify the condition (or enter a formula)
e) Click the Format Button and specify the Formatting to apply of the conditions is true
f) To add conditions (up to three). Click Add and then repeat steps 3 through e.
g) Click OK.

After you have performed these steps, the cell or range will be formatted based on the condition(s) you specify. This Formatting, of course, is dynamic. If you change the contents of a cell, Excel re-evaluates the new contents and applies or removes the Formatting accordingly.

*Formatting types you can apply*

When you click on the Format button in the Conditional Formatting dialog box, you get the format cells dialog box as shown in picture below. This is a modified version of the standard format cells dialog box. it does not have the number, alignment, and the Protection Tabs, and it includes a Clear button.


*Specifying conditions*

The left most drop-down list in the Conditional Formatting dialog box (refer to picture 2 above) enable you to choose one of two options:

a) Cell Value is: For simple conditions
b) Formula Is: For more complex, formula based conditions

Let's understand these two in details.

_Cell Value is: For simple conditions_

When you select cell value is, you can specify conditions of the following types:

a) Between (You specify Two Values)
b) Not Between (You specify Two Values)
c) Equal to (You specify One Value)
d) Not Equal to (You specify One Value)
e) Greater than (You specify One Value)
f) Less than (You specify One Value)
g) Greater than or Equal to (You specify One Value)
h) Less than or equal to (You specify One Value)

You can enter the value(s) directly or specify a cell reference.

_Formula Is: For more complex, formula based conditions_

When you select formula is, you can specify a formula. Do so by specifying a cell that contains a formula or by entering a formula directly into the Conditional Formatting dialog box. As with normal excel formulas, the formulas you enter here must begin with an equal sign (=).

For Example

a) Select A1:A5 and ensure that the cell A1 is the active cell.
b) Choose Format=>Conditional Formatting. The Conditional Formatting dialog box appears (refer to t picture1 above)
c) Select the *Formula Is* item in the drop down list.
d) Enter the formula in the formula box
=A1=""


e) Click the format button to display the format cells dialog box
f) In the format cells dialog box, specify a pattern for the cell shading and click OK to return to the Conditional Formatting dialog box.
g) Click OK to close the Conditional Formatting dialog box.

Notice that the formula entered contains a reference to the upper-left cell in the selected range, to demonstrate that the reference is relative, select cell A5 and examine its Conditional Formatting formula. Youll see that the Conditional Formatting formula for the cell is

=A5= 

Generally, when entering a Conditional Formatting formula for a range of cells, youll use a reference to the active cell  which is normally to the upper-left cell in the selected range. One exception is when you need to refer to a specific cell.

For example, suppose that you select Range A1:B5 and you want to apply Formatting to all cells in the range that exceeds the value in cell C1. Enter this Conditional Formatting formula:

=A1>$C$1

In this case, the reference to cell C1 is an absolute reference; it will not be adjusted for the cells in the selected range. In other words, the Conditional Formatting formula for cell A2 looks like this:

=A2>$C$1

The relative cell reference is adjusted, but the absolute cell reference is not.

*Working with Conditional formats*

This section describes some additional information about Conditional Formatting that you might find useful.

_a) Multiple Conditions_
You can specify as many as three conditions by clicking the Add button in the Conditional Formatting dialog box (refer to picture 2). For example, you might enter the following three conditions (and specify different Formatting for each):

Cell value is less than 0
Cell value is equal to 0
Cell value is greater than 0

In this case, The sign of the value (negative, 0, or positive) determines the applied Formatting.

If none of the specified conditions is TRUE, the cells keep their existing formats. If you specify multiple conditions and more than one condition is TRUE for a particular cell, excel applies only for the Formatting for the first TRUE condition. For example, you may specify the following two conditions:

Cell value is between 1 and 12
Cell value is less than 6

Entering a value of 4 satisfies both conditions. Therefore, the cell will be formatted using the format specified for the 1st condition.

_b) Things we need to take care while pasting_

We need to be very careful; while pasting copied data as its very easy to wipe out the Conditional Formatting in a cell or range.

*c) Copy cells which contain Conditional Formatting*

Conditional Formatting information is stored with a cell much like standard Formatting information is stored in a cell. This means when you copy a cell that contains Conditional Formatting, the Conditional Formatting is also copied.

Inserting rows or columns within a range that contains Conditional Formatting causes the new cells to have the same Conditional Formatting.

*d) Deleting Conditional Formatting*

To remove only Conditional Formatting (and leave other Formatting intact), you need to use the Conditional Formatting dialog box.

Select the cells; then choose Format->Conditional Formatting. The Conditional Formatting dialog box appears.
Click the delete button in the Conditional Formatting dialog box. The delete Conditional dialog box appears

In the delete Conditional Formatting dialog box, specify the conditions that you want to delete. This dialog boxes always display check boxes for three conditions, even if you havent defined many.


Click OK to dismiss the delete Conditional Format dialog box, and then click on OK again to close the Conditional Formatting Dialog Box.

*e) Locating cell that contain Conditional Formatting*

You cannot tell, just by looking at a cell, whether it contains Conditional Formatting. You can, however, use Excels Go-To Dialog box to select such cells.
Select Edit->Got To (or press F5) to display the Go-To Dialog Box.




In the Go-To Dialog Box, Click the special button. The Go-To Special Dialog box appears (As show in picture below)


Select the Conditional Formats option
To select all cells on the worksheet containing Conditional Formatting, Select the All option. To select only the cells that contain the same Conditional Formatting as the active cell, select the same option.
Click OK and Excel selects the cell for you.

*f) Using Reference to other sheets*
If you enter a Conditional Formatting formula that uses one or more references to other sheets, excel responds with an error message. If you need to refer to a cell on a different sheet, you must create a reference so that cell on the same sheet contains the Conditional Formatting. For example, if you Conditional Formatting formula needs to refer to cell A1 on sheet 3, you can insert the following formulas into a cell on the active sheet.
=Sheet3!A1

Then use a reference to that cell in you Conditional Formatting formula.

*Some Conditional Formatting Formula Examples*

a) Identifying non numeric data
=ISTEXT(A1)
b) Identifying dates in particular month
=MONTH(A1)=6
c) Identifying todays date
=A1=TODAY()
d) Identifying maximum value in a range
=A1=MAX=($A$1:$A$30)

----------


## Siddharth Rout

Example 1 Using (Cell Value Is)

*Condition:* Checking if the cell is blank.

Simply follow instructions in the picture.

----------


## Siddharth Rout

Example 2 Using (Formula Is)

*Condition:* Checking if the value entered in the cell is 'Text'

Simply Follow Instructions in the Picture

----------

