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?