Hi, I am trying to format excel file base on date. I have a excel file with lots rows in which a cell contains date info. I want to format it like this: If the date in the line is less than today, change the row to red; If the date is between today and + 7 days change the row to yellow; If the date is greater than today + 7 days chnage the row to green; I want to use marco so that every time I open the file it will auto update the format. Can this be done and how? Thanks in advance

Just to clarify, I want to change the color for a row not just a cell

Unless you aren't telling us the whole the story, you don't need a macro to do this. This can be done with Conditional Formatting. Conditional formatting is limited to 3 conditions, so it should fit your needs. I'm going to assume your date is in A1. You'll need to adjust your formulae to match your exact situation. - Select Row 1. - Choose Format...Conditional formatting - Under Condition 1, use the dropdown to choose Formula is - Enter =IF($A$1<TODAY(),TRUE, FALSE) in the blank box. - Click the Format box, the Pattern tab and choose Red - Click Add - Under Condition 2, use the dropdown to choose Formula is - Enter =IF(AND($A$1>=TODAY(),$A$1<=TODAY()+7),TRUE, FALSE) - Click the Format box, the Pattern tab and choose Yellow - Click Add - Under Condition 3, use the dropdown to choose Formula is - Enter =IF($A$1>TODAY()+7,TRUE, FALSE) - Click the Format box, the Pattern tab and choose Green - Click OK Note: Your second condition says: "If the date is between today and + 7 days change the row to yellow" The way I read it, that does not include today or today + 7. In fact, none of your conditions include today or today + 7 (in my opinion). The formula I used for Condition 2 does in fact include today and today +7. You can adjust any or all of the formulae to match your exact needs.

Thanks DerbyDad03 Sorry for the newbie question -- I am not good at excel and using "fomula is" fuction. I tried your way and it applied to only one row. How can I apply to the entire sheet(all rows)? btw, your are right. For the yellow color one I mean to include today and +7 day. Thanks for your help

my sample file like this: January 1, 2008 dfgsd -- in red February 2, 2008 dfgsdf -- in red March 3, 2008 sdfgsdf -- in red March 25, 2008 gsdfg -- in yellow March 26, 2008 sdfgsdf -- in yellow March 23, 2008 sdfgsdf -- in yellow April 8, 2008 sdfgsdf -- in green April 9, 2008 sdfgsdf -- in green April 11, 2008 sdfgsdf -- in green April 2, 2008 sdfgsdf -- in green March 27, 2008 sdfgsdf -- in yellow March 17, 2008 sdfgsdf -- in red March 28, 2008 gsdfg -- in yellow March 29, 2008 gsdfg -- in yellow March 30, 2008 gsdfg -- in red Thanks

My example was for an assumed date in A1, so I only gave the instructions for that row. If you want to conditionally format more rows, select them and follow the steps above. The only change to the formulae would be to eliminate the $ in front of the row numbers so that each row picks up the formatting for that row. e.g. If your dates are in column A, and assuming the first one is in Row 1, the formula for Condition 2 would be: =IF(AND($A1>=TODAY(),$A1<=TODAY()+7),TRUE, FALSE) Be carefull though - Excel can sometimes be picky about how many cells it will allow to formatted. If you have too much formatting in a spreadsheet, you might get errors when you try to save it.

Thanks DerbyDad03 It works exactly as what I want. One more question, I did a select entire sheet and change the background color if it meets the condition. I got an issue here-- for the blank cell since it has no date in it. the formatting treat it as <today() so it change the all blank rows to "red". How can I eliminate it? I guess I need a condition(<> blank), right?, and how? The reason why I selected entire row is that I want to be able to add/delete/modify the rows Thanks