How to use Conditional Formatting in Excel 2007

Conditional Formatting is probably one of my favorite things in Excel – the possibilities are probably nearly endless.

This post and video will explain two uses for Conditional Formatting:

1. How to have cells recolored automatically based on the text in the cell
2. Automatically alternate row colors using a macro and formula

In the video the sample water and sewer billing spreadsheet for a 200+ unit apartment building. Since tenants come and go we want to easily be able to visually see what units are vacant. I have set up Conditional Formatting to automatically color any cell that reads “vacant”.

The other use of Conditional Formatting in this same sheet is using a macro to automatically alternate the color on each row of data. (primary purpose is to just make the sheet easier on the eyes)

**IMPORTANT** prior to running any Conditional Formatting you must highlight the desired range of data

Steps to set up coloring of cells based on specific text

• Home tab>Conditional Formatting
• New Rule
• Format only cells that contain **(in bottom section of the screen fill out your desired criteria and be certain to chose a color – which is the “format”)
• Select OK

**In this example I chose “Specific Text”>Containing>Vacant

Steps for Automatically Alternating the Row Color is explained on this previous post. If you plan on using this frequently it would be beneficial to record a macro so that you do not have to remember the formula listed below.

• Home tab>Conditional Formatting
• New Rule
• Use a formula to determine which cells to format (again, be sure to chose your desired format)
• Select OK

The formula to be used is: =MOD(ROW(),2)=0

How to record a macro in Excel 2007:

• View Tab>Macros
• Record Macro (record using the steps outlined above)
• Stop Recording

**Be sure to save the Macro with an easy to remember name and / or shortcut and to save it in your personal macro workbook.

What are your favorite uses for Conditional Formatting?