Conditional formatting based on dates in a range of cells

Hi,

I'm new to this forum so please excuse any "newbie" errors - I've trawled through lots of posts to try and find an answer to my specific problem but have drawn a blank...

Basically, I have a spreadsheet for a team at work with a list of dates when certain qualifications/training expire. I'm trying to use conditional formatting to highlight the first cell (name) in a row if any of the dates in the rest of the row have passed. So for example, I would like cell B1 to fill red if the dates in any of cells B2:B30 are earlier than TODAY, or alternatively are earlier than cell A1 (where I can use the TODAY() function as a workaround).

I’m happy using conditional formatting to highlight either the date cells themselves or the first cell based on one other cell, but I’m struggling to apply the formatting to one cell based on a range of cells.

Any help you can provide would be most appreciated!
Cheers,
Paul

I am also new to this and have a similar question I have employee's with expiration dates in my spreadsheet as in Driver License etc. I would like the cell with the date to turn red one month before the date in the cell as to remind me to notify them.
Can anyone tell me how to do this???

SXLDanny - that's relatively straightforward I think - just highlight the cells you want to format and click on Conditional Formatting.
Create a new rule to "format only cells that contain...", select "less than" in the drop down box and, in the box on the right, enter "=TODAY()+30" (without inverted commas). Then select the formatting you want and away you go!

*Bump* as no answer to original question - if it's not possible please put me out of my misery!
Ta, Paul

Originally Posted by pmerobertson

SXLDanny - that's relatively straightforward I think - just highlight the cells you want to format and click on Conditional Formatting.
Create a new rule to "format only cells that contain...", select "less than" in the drop down box and, in the box on the right, enter "=TODAY()+30" (without inverted commas). Then select the formatting you want and away you go!

Thank you for trying to help me. I Tried this about 5 times slowly and still can't figure it out guess i'm stupid.

If I have a date say 3/15/2013 in a cell and I want that cell to turn red 30 days prior to that date > how do i conditional format that cell.

This page might help - sets it out pretty clearly http://www.techonthenet.com/excel/qu...rmat4_2007.php

Thank you very much pmerobertson that was very helpful...... I've wanted to get this done for along time.... Got it working....

> .. So for example, I would like cell B1 to fill red if the dates in any of cells B2:B30 are earlier than TODAY

One way, as real dates are just numbers ....
Apply CF on B1 using Formula:
=SUMPRODUCT((B2:B30<>"")*(B2:B30<TODAY()))>0
----------
Success? Wave it, hit the little star at the bottom left of my response

Max you're a genius! Thank you very much...