Highlighting cells with specified dates

[MC]

I have a worksheet that keeps track of our coaches certifications. There are
three columns which contain dates (specifically columns g, h & I) I would
like to be able to have excel automatically scan those columns and highlight
cells that are equal to or lesser than a specific date. This function should
also allow me to change the date several times a year. Can someone suggest
the easiest way of performing that task? I have provided an example below.

Name Sport First Aid CPR Permit
John Doe Soccer 10/31/07 10/31/08 12/11/09
Jane Doe Basketball 03/10/08 04/19/07 11/05/06
I would like to automatically highlight dates that are equal to or prior to
10/31/07.

 

[Don Guillett]

put your variable date into a cell somewhere and use conditional formatting
to refer to it
format>conditional formatting>formula is>
=f2<=$f$1

 

[ed]

I have a worksheet that keeps track of our coaches certifications. There are
three columns which contain dates (specifically columns g, h & I) I would
like to be able to have excel automatically scan those columns and highlight
cells that are equal to or lesser than a specific date. This function should
also allow me to change the date several times a year. Can someone suggest
the easiest way of performing that task? I have provided an example below.

Name Sport First Aid CPR Permit
John Doe Soccer 10/31/07 10/31/08 12/11/09
Jane Doe Basketball 03/10/08 04/19/07 11/05/06
I would like to automatically highlight dates that are equal to or prior to
10/31/07.

If your array of dates starts on cell G2 Conditional Format cell G2
to CellValue is = "Less Than or Equal to" and put either 10/31/07
or the cell where you can put a date to refer to (such as $J$2. If
you do this be sure to include the $s), and select your formatting.
Then use the yellow format painter to apply that CD to all the date
cells in column g,h and I and put 10/31/07 in cell J2. Changing the
date in J2 will change the dates that are formatted.

ed