Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))" |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))" |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))" |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional format error Excel 2007 | Excel Worksheet Functions | |||
Date format error | Excel Discussion (Misc queries) | |||
Date-error Format | Setting up and Configuration of Excel | |||
RANDBETWEEN in a conditional format gives error | Excel Worksheet Functions | |||
Check if Conditional Format is True or False / Check cell Color | Excel Worksheet Functions |