Excel 2007 Conditional Formatting - Based on Date?

You don't need the Today() function on the other sheet since you can use it in your conditional format formula. I'm assuming that you already know how to create your data validation list in column F.

Select the range of cells that for which you want the rule to apply (say, A7:F25, assuming that you have a whole list of contacts in that range). Go to Home and click Conditional Formatting in the Styles group. Click on New Rule and the click the last item in the list of rule types (Use a formula...). Now, under "Format values where this formula is true" type this formula:

=TODAY()-$F7>=$D7

Set your format (red background) and then click OK. This will apply the conditional format to each row in the range, depending on the last contact date and the number of days that you have specified in that row.

Tim

http://www.tvmcalcs.com/

1.) Select the range of cells that you want to have conditional formatting.

I recommend that you do not select the entire sheet, nor the entire column/row. Instead select the desired table (region). [Conditional Formatting slows down the calculations in your spreadsheet. It is best to minimize the cells affected.] For purposes of illustration, I will assume that you have column headers in row 1, that your table consists of an array of data in A2:Z100, and that there are no locked cells.

2.) On the menu bar, go to Format | Conditional formatting.

You already knew this step. :-)

3.) In the first drop-down menu, select "Formula Is."

Your desired results require more manipulation than the "Cell Is" option allows.

4.) Type the following formula:

=IF ( SUM ( $D2, $F2 ) = TODAY (), TRUE, FALSE )

Spaces are included so that Y!A does not truncate the formula, and so that you can easily read it. Do not actually include spaces in your formula. Note that I referenced row 2 because you have a range of cells selected that begins on row 2. If the only row you have selected is row 7, as in your example, then all the references to row 2 should instead be row 7.

5.) Click the "Format..." button, and select the desired formatting

This is where you select the desired format if the reminder date is equal to today. Per your description, I would choose bolded white text, and a solid red pattern. You may find that bolded red text or pink shading is a little easier on the eyes.

6.) Click "OK" when you are done selecting your formatting.

7.) Click "OK" on the Conditional Formatting dialog box.

This should automatically apply your conditional formatting.

One point: it doesn't matter how the cells are formatted, it depends on the value that is formatted. 12/15/2010 and 12/16/2010 will both show, with your format, as 12-10, but will not be equal. You would have to check =and(year(a2)=year($a$12),month(a2)=mont...