Conditional formatting refrences/validity

Some text not appearing in message - look in comments box

Have you Added-in the Analyis ToolPak? [nt]

Yes, it is just the formula I can't get

Here's a repost of your original posting... so I can read it!

I have a spreadsheet in which cell f2 is a date based on the formula =IF(C2< TODAY(),DATE(YEAR(C2)+G2,MONTH(C2),DAY(C2)),C2) with c2 being a date and g2 the no. of years in advance.

I want cell f2 (or the entire row if possible) to turn red if the date in f2 is less than today()
I entered =F2< TODAY()

I also want cell f2 (or the entire row) to turn green if the date is within the next year.
I entered =AND(F2>=TODAY(),F2<=edate(TODAY(),12))
This does not work.

As I intend to create a macro (somehow) that will show on another sheet all the properties between the date in f2 and a year from then, what is my best course of action?

Will i need to enter even more macros instead of conditional formatting?

Pleeeeeeeeaase help
thanks
George

Please understand...

That... "If you specify multiple conditions and
more than one condition is true, Microsoft Excel
applies only the formats of the first true
condition." This is the text of a tip found
on the help topic for "Apply conditional formats
to cells".

So, when F2 is < TODAY() you'll never test the
2nd condition. Is that the case?

Also...

I can't seem to get EDATE() into a condition
without receiving a "You may not use references
to other worksheets..." error message. Remember,
EDATE() is provided by the Analysis ToolPak (which
is another specialized worksheet). There is a
workaround... Enter =EDATE(TODAY(),12) into another
worksheet cell (say, H1) and use...

=AND($F2>=TODAY(),$F2<$H$1)

Using this approach I an format row 2:2 as either
red or green depending on the contents F2.

Thanks Mark

As this spreadsheet is meant to be for a report, I was hoping that there would be no need to enter another column, even though I can hide it. Just though there would be another way to address the period between 2 dates problem.

Thanks for the input
George