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:
- How to have cells recolored automatically based on the text in the cell
- 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?