Conditional format with error & date check
 

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))"

Conditional format with error & date check
 

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 FormatConditional 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

--
Biff
Microsoft Excel MVP


"RobofMN" wrote in message
...
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))"

Conditional format with error & date check
 

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" wrote:

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 FormatConditional 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

--
Biff
Microsoft Excel MVP


"RobofMN" wrote in message
...
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))"

Conditional format with error & date check
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"RobofMN" wrote in message
...
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" wrote:

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 FormatConditional 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

--
Biff
Microsoft Excel MVP


"RobofMN" wrote in message
...
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))"