Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Conditional format when #N/A
Hi,
I have a Vlookup function in column H of a worksheet (which is written in code as part of a macro). I want to do conditional formatting if the vlookup result is #N/A (which I would also like to be part of my macro). I need to do this for all the cells in column H, not just one specific cell. In the worksheet, I may or may not have a #N/A to highlight. Any help would be GREATLY APPRECIATED...as I have been trying to figure this out for 3 hours now. Thanks, Sara |
#2
|
|||
|
|||
if(iserror(vlookup(blah blah blah),1,vlookup(blah blah blah))
basically, this changes teh result of the formula to 1 instead of #N/A. Now you can do a conditional format on the 1. I'd say make the 1 a text string, but vba doesn't like quotes "Frantic Excel-er" wrote: Hi, I have a Vlookup function in column H of a worksheet (which is written in code as part of a macro). I want to do conditional formatting if the vlookup result is #N/A (which I would also like to be part of my macro). I need to do this for all the cells in column H, not just one specific cell. In the worksheet, I may or may not have a #N/A to highlight. Any help would be GREATLY APPRECIATED...as I have been trying to figure this out for 3 hours now. Thanks, Sara |
#3
|
|||
|
|||
Joe,
the problem occurs when the value in E isn't in my lookup table. Here is my code: With ActiveCell RowCount = .Offset(0, -1).End(xlDown).Row - .Offset(0, -1).Row + 1 .FormulaR1C1 = _ "=IF(RC[-4]=""C"",VLOOKUP(RC[-3],USB,2,FALSE),VLOOKUP(RC[-3],FAS,2,FALSE))" .AutoFill .Resize(RowCount) End With the formula is actually an if statement with 1 vlookup if column "C" has a "C" in it, and another vlookup if it doesn't have a "C" in it. So, I am not sure where the #N/A is coming from, but I believe it is because the value isn't in the lookup table. AAAAAGGGGHHHHH.... Any more ideas...I really appreciate the help.... "Joe" wrote: if(iserror(vlookup(blah blah blah),1,vlookup(blah blah blah)) basically, this changes teh result of the formula to 1 instead of #N/A. Now you can do a conditional format on the 1. I'd say make the 1 a text string, but vba doesn't like quotes "Frantic Excel-er" wrote: Hi, I have a Vlookup function in column H of a worksheet (which is written in code as part of a macro). I want to do conditional formatting if the vlookup result is #N/A (which I would also like to be part of my macro). I need to do this for all the cells in column H, not just one specific cell. In the worksheet, I may or may not have a #N/A to highlight. Any help would be GREATLY APPRECIATED...as I have been trying to figure this out for 3 hours now. Thanks, Sara |
#4
|
|||
|
|||
How about just plain old vanilla
=ISNA(H1) -- HTH Bob Phillips "Frantic Excel-er" wrote in message ... Joe, the problem occurs when the value in E isn't in my lookup table. Here is my code: With ActiveCell RowCount = .Offset(0, -1).End(xlDown).Row - .Offset(0, -1).Row + 1 .FormulaR1C1 = _ "=IF(RC[-4]=""C"",VLOOKUP(RC[-3],USB,2,FALSE),VLOOKUP(RC[-3],FAS,2,FALSE))" .AutoFill .Resize(RowCount) End With the formula is actually an if statement with 1 vlookup if column "C" has a "C" in it, and another vlookup if it doesn't have a "C" in it. So, I am not sure where the #N/A is coming from, but I believe it is because the value isn't in the lookup table. AAAAAGGGGHHHHH.... Any more ideas...I really appreciate the help.... "Joe" wrote: if(iserror(vlookup(blah blah blah),1,vlookup(blah blah blah)) basically, this changes teh result of the formula to 1 instead of #N/A. Now you can do a conditional format on the 1. I'd say make the 1 a text string, but vba doesn't like quotes "Frantic Excel-er" wrote: Hi, I have a Vlookup function in column H of a worksheet (which is written in code as part of a macro). I want to do conditional formatting if the vlookup result is #N/A (which I would also like to be part of my macro). I need to do this for all the cells in column H, not just one specific cell. In the worksheet, I may or may not have a #N/A to highlight. Any help would be GREATLY APPRECIATED...as I have been trying to figure this out for 3 hours now. Thanks, Sara |
#5
|
|||
|
|||
Bob.....
I want to thank you....I have tried that about 18000 times today, and it didn't work....but I just now realized that I have to be on Cell H1, and then highlight the whole column for it to work..... AAAGGGHHH...can't believe I have wasted 4 hours of my life with such a studip little detail.... Ah well.....guess you live and learn... Can say that I have learned one thing from this...I will always make sure that my cursor is on the 1st row of whichever column I am trying to format... LOLOLOLOLOL Thanks... Sara "Bob Phillips" wrote: How about just plain old vanilla =ISNA(H1) -- HTH Bob Phillips "Frantic Excel-er" wrote in message ... Joe, the problem occurs when the value in E isn't in my lookup table. Here is my code: With ActiveCell RowCount = .Offset(0, -1).End(xlDown).Row - .Offset(0, -1).Row + 1 .FormulaR1C1 = _ "=IF(RC[-4]=""C"",VLOOKUP(RC[-3],USB,2,FALSE),VLOOKUP(RC[-3],FAS,2,FALSE))" .AutoFill .Resize(RowCount) End With the formula is actually an if statement with 1 vlookup if column "C" has a "C" in it, and another vlookup if it doesn't have a "C" in it. So, I am not sure where the #N/A is coming from, but I believe it is because the value isn't in the lookup table. AAAAAGGGGHHHHH.... Any more ideas...I really appreciate the help.... "Joe" wrote: if(iserror(vlookup(blah blah blah),1,vlookup(blah blah blah)) basically, this changes teh result of the formula to 1 instead of #N/A. Now you can do a conditional format on the 1. I'd say make the 1 a text string, but vba doesn't like quotes "Frantic Excel-er" wrote: Hi, I have a Vlookup function in column H of a worksheet (which is written in code as part of a macro). I want to do conditional formatting if the vlookup result is #N/A (which I would also like to be part of my macro). I need to do this for all the cells in column H, not just one specific cell. In the worksheet, I may or may not have a #N/A to highlight. Any help would be GREATLY APPRECIATED...as I have been trying to figure this out for 3 hours now. Thanks, Sara |
#6
|
|||
|
|||
Glad to be of service :-)
"Frantic Excel-er" wrote in message ... Bob..... I want to thank you....I have tried that about 18000 times today, and it didn't work....but I just now realized that I have to be on Cell H1, and then highlight the whole column for it to work..... AAAGGGHHH...can't believe I have wasted 4 hours of my life with such a studip little detail.... Ah well.....guess you live and learn... Can say that I have learned one thing from this...I will always make sure that my cursor is on the 1st row of whichever column I am trying to format... LOLOLOLOLOL Thanks... Sara "Bob Phillips" wrote: How about just plain old vanilla =ISNA(H1) -- HTH Bob Phillips "Frantic Excel-er" wrote in message ... Joe, the problem occurs when the value in E isn't in my lookup table. Here is my code: With ActiveCell RowCount = .Offset(0, -1).End(xlDown).Row - .Offset(0, -1).Row + 1 .FormulaR1C1 = _ "=IF(RC[-4]=""C"",VLOOKUP(RC[-3],USB,2,FALSE),VLOOKUP(RC[-3],FAS,2,FALSE))" .AutoFill .Resize(RowCount) End With the formula is actually an if statement with 1 vlookup if column "C" has a "C" in it, and another vlookup if it doesn't have a "C" in it. So, I am not sure where the #N/A is coming from, but I believe it is because the value isn't in the lookup table. AAAAAGGGGHHHHH.... Any more ideas...I really appreciate the help.... "Joe" wrote: if(iserror(vlookup(blah blah blah),1,vlookup(blah blah blah)) basically, this changes teh result of the formula to 1 instead of #N/A. Now you can do a conditional format on the 1. I'd say make the 1 a text string, but vba doesn't like quotes "Frantic Excel-er" wrote: Hi, I have a Vlookup function in column H of a worksheet (which is written in code as part of a macro). I want to do conditional formatting if the vlookup result is #N/A (which I would also like to be part of my macro). I need to do this for all the cells in column H, not just one specific cell. In the worksheet, I may or may not have a #N/A to highlight. Any help would be GREATLY APPRECIATED...as I have been trying to figure this out for 3 hours now. Thanks, Sara |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keep conditional format when "show pages" from Pivot table | Excel Discussion (Misc queries) | |||
How do I do a conditional format for numbers | Excel Worksheet Functions | |||
Office2000: Conditional format behaves strangely | Excel Discussion (Misc queries) | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) | |||
Copying a conditional format | Excel Worksheet Functions |