Conditionally format a range in Excel 2003?

[Carson Dyle]

I'm using Excel 2003. Is there a way to conditionally format a row of cells based on a condition in one cell? I have a date, that when it passes, I want to color the whole row red for "expired" or "past due".

[Aardigspook]

Just highlight the whole range and use a static cell reference in the formula - something like this:

Please Login or Register  to view this content.

Edit: If you want to be able to drag this down rows, leave the $ sign off the number ($A1). If you want to drag across columns, take the $ off the letter (A$1).

Edit2: changed > to < in formula (d'oh!).

[Carson Dyle]

Thank you. That worked perfectly. Less than TODAY() for expiration.

[Aardigspook]

Less than TODAY()

Doh! Oops, well-spotted, sorry!

Thanks for the feedback. If that solves your problem, then please mark the thread as solved, so others know there's an answer here (instructions in my sig).

[Carson Dyle]

One additional thing I need to add. Sometimes I want to leave the expiration date blank. Doing so causes Excel to mark the row 'expired', so I guess the blank date evaluates as less than TODAY(). Also, as I'm entering an item that will have an expiration date, the row is marked as expired until I get to the column with the date and fill it in, which is a small annoyance.

Can this be fixed in the formula?

* Edit *

I think I figured it out, but I know that dates can be tricky. Would this be the best way?

=AND($F19<>"",$F19<TOD())

[Carson Dyle]

n/m. I think that works.