How to change background color of a cell to black if value is "#N/A" returned from vlookup

[ohlalayeah]

How to change background color of a cell to black if value is "#N/A" returned from vlookup? I want to use conditional formatting.

Attached the file, pls help how to deal with F19:F38.

Thanks!

[Ace_XL]

Do you want the "#N/A" to show up. You could eleiminate it altogether

Use in Cell F20
=IF(P20="","",IF(ISNA(VLOOKUP(D20,INDIRECT(SUBSTITUTE(VLOOKUP(LEFT($D$15,10),SizevPantone,4,FALSE),"Pantone","")),2,FALSE)),"",VLOOKUP(D20,INDIRECT(SUBSTITUTE(VLOOKUP(LEFT($D$15,10),SizevPantone,4,FALSE),"Pantone","")),2,FALSE)))

Alternatively, you could use conditional formatting to format cells with 'errors' as black

[dilipandey]

Hi Ohlalayeah,

You were following correct approach, just the formula in conditional formatting need to be :-
=ISERROR(F19)

see attachment:- Development_Swing Ticket-20120709.xls


Regards,
DILIPandey

<click on below 'star' if this helps>

[Ace_XL]

The IFERROR() suggestion will not work in Excel 2003 though!

[dilipandey]

yes.... you are right Ace_XL, and that's why I have suggested IsError instead of IfError

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>