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

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

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

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

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

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