Formatting a range of cells based on a date

Hi, I have a date in a cell that when it is less than today() (so yesterday's date) would turn red and the 5 cells to its immediate right would also turn red, if the date is still current then it would be green and so would the right hand cells.

How do I do this as its driving me nuts.

Cheers in advance

JD

What is your question? How to format your dated cells to be colored with your parameters?

You would use Conditional Formatting, using a formula. Assume your date is in column A and so the effect needs to apply to columns A-F. Assume also that you have a header row, so your data begins on row 2 (it's always useful to state which cells you are using). Select the data from A2 to F-whatever, then click on Conditional Formatting | New Rule and choose the option Use a Formula... from the bottom of the list. Enter this formula in the dialogue box:

=AND($A2<TODAY(),$A2<>"")

then click the Format button then the Fill tab and choose Red. OK your way out, and Excel will apply that format by adjusting the cell references within the selected range. You can do the same for green, but you would use this formula:

=$A2>=TODAY()

Hope this helps.

Pete

Cheers Pete