Conditional format with error & date check

[RobofMN]

I want a column to have any cells with #NA turn red if the today's date is
after a certain date. I'm sure I could use a specific cell reference such as
a2,a3,etc. and copy/pase special a formula down the rest of the column but
I'm wondering if there was a way to select the column and set a single
formula to do the job. Below is a couple examples that failed.

="and(iserror{at:at},today()<date(2008,1,1))"
="and(iserror(at:at),today()<date(2008,1,1))"

 

[T. Valko]

Well, you have to use relative cell references and you can apply the cf all
at one time.

Assume your data is in the range A2:A10

Select the entire range A2:A10 starting from cell A2. A2 will be the active
cell. The active cell is the one cell in the selected range that is not
"blueish".

Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the little box on the right:

=AND(ISNA(A2),TODAY()<DATE(2008,1,1))

Click the format button
Select the desired style(s)
OK out

 

[RobofMN]

Thank you, worked great!

For some reason I had wrongly been thinking that if I referenced a single
cell instead of a range it wouldn't apply the format to the range I had
highlighted. I was forgetting that the "Applies to" field would take care of
the rest of the range.

 

[T. Valko]

You're welcome. Thanks for the feedback!