Auto-Highlight Records Through Conditional formatting for Excel 2007 and Excel 2010

Auto-highlight a row can be easily achieved if you know conditional formatting in Excel. As the name implies, it is about formatting a cell based on some preset conditions. These conditions could be based on the content within the cell or from some other cells. In Excel 2007, there is no limit on the number of conditions you can define for the cell. Here is an example on how the conditional format can auot-highlight dates.

In the picture below, we wish to highlight the rows RED when the date displayed in column C that is less than 30 dates from today and highight YELLOW when it is more than 30 days but less than 60 days from today.

Using conditional formats to auto-highlight the rows.

1. Select the range A2:D5 you wish to set the conditional format.

2. In the Home Tab/Ribbon, click on Conditional Formatting.

3. In the dropdown menu, select Manage rules.

4. In the pop-up window, click on the button "New Rule".

5. In the next pop-up window, choose the rule Type "Use a formula to determine which cells to format. In the box below, we enter the first rule for Red colour. Type in without quotes
=($C3-TODAY())<30
Take note of the dollar sign. There isn't one before the number 3. This is to make the conditional format function look for the relative dates in the subsequent rows. The TODAY() formula will all use today date for the calculation. Then click on the Format button to set the format to fill the cell with RED when the condition is met. Click on the OK button twice to go back to the window titled "Condtional Formatting Rules Manager"

6. Click on the "New Rule" button again.

7. In the next pop-up window, choose the rule Type "Use a formula to determine which cells to format. In the box below, we enter the 2nd rule for Yellow colour. Type in without quotes
=AND(($C3-TODAY())>=30,($C3-TODAY())<60)
The AND formula is to make sure than both conditions (separated by comma) must be met before the conditional format will highlight the cell yellow. Then click on the Format button to set the format to fill the cell with YELLOW when the condition is met. Click on the OK button twice to go back to the window titled "Condtional Formatting Rules Manager"

8. In the "Condtional Formatting Rules Manager" window, check on the box below the decription STOP if TRUE. This is to stop conditional formatting from checking the condition once the condition is met. Click Apply and Close.

Now, row 4 is auto-highlighted with YELLOW because the DATE COMPLETED is more than 30 days but less than 60 days from TODAY (calculation done on 14 Apr 2011).

Row 5 is highlight RED because the DATE COMPLETED is less than 30 days from TODAY.

Because we enter the formula TODAY() as part of the condition, the check will refresh on a daily basis and auto-highlight the cells using today's date as a reference.

Introduction to auto-highlight / conditional formatting


New! Comments

Have your say about what you just read! Leave me a comment in the box below.