Excel 2007 : Changing row colour if column D date is before current system date

Have a 'competitions entered' spreadsheet which contains a closing date for each competition in column D. I'd like a formula or macro which compares each date in column D and if the date is earlier than todays date format fill the background of that row to a grey colour.

Would be grateful for any suggestions to achieve the above please.

Use conditional formatting. Let's say the columns of data are A:M starting at row 2.

1) Highlight A2:M200 (or however many rows down you want)
2) Click Format > Conditional Formatting (or whatever the equivalent Excel 2007 to open the CF window)
3) Use Condition1: Formula Is: =AND($D2>0,TODAY()>$D2)
4) Use Format... Patterns... Grey color

Many thanks JB that worked a treat (see attachment). Out of interest is there a way of carrying out this formatting for the whole worksheet automatically without having to manually select rows each time I add new ones?

As demonstrated in my instructions above, you should select the entire range at once and fill out the formula based on the active row (the primary row that is shown as the first row you selected when you chose the whole range).

In my instructions, if you started and A2 and highlighted all the data through M200, row 2 is still the primary row, so the formula should be entered as shown. By using a $D you are locking the column to column D for all rows, but by not using a $ in front of the 2 in $D2, that leaves the row reference relative and it will adjust automatically down the whole data set.

Follow those instructions above explicitly. You do not need to do this row by row.

Trevor, follow the instructions given above TO THE LETTER. My example indicates selecting 200 rows of data to apply the formula to all at once. Surely you see that?

I do not recommend you apply any conditional formatting to your entire sheet, to use your words. Select a number of rows sufficient for your needs, 1000s of rows even, just not all of them!

Then use the formula given. You should only have to apply the formula ONCE for the entire range of rows you select. You will select a lot of rows, just do it.

1) Highlight A2:M200 (or however many rows/columns you want)
2) Click Format > Conditional Formatting (or whatever the equivalent Excel 2007 to open the CF window)
3) Use Condition1: Formula Is: =AND($D2>0,TODAY()>$D2)
4) Use Format... Patterns... Grey color

An alternate method would be to highlight a row you've already successfully formatted, click on the FORMAT PAINTER icon, and then select all the other rows to paint that formatting into.

(chuckle) It is very simple to miss pieces of steps, do it myself all the time, so being emphatically pointed back to the original instructions is a legitimate response, yes?

It is also not uncommon to get questions that basically equate to, "I didn't fully try what you told me yet, but will this work?" Again, I've caught myself doing that very thing.

So, obnoxious tips of the day:
1) Be meticulous when someone provides step-by-step instructions
2) Be sure to fully try the suggested solution before pulling apart the method as suspect.

I know this works, so I hope you got it working, if not with the first method, then by the alternate method suggested in post #7.

As you can see, above all else, I am trying to make sure you get the results you want.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].