Conditional Formatting Help

[Lewzerrrr]

Hi

I have a sheet that has 20 boxes, 5 by 4, B4:D20, F4:H20... R58:T74

In each one has a serial number where my macro extracts an image from web into the box e.g B4:D15 = Picture, C16 = Serial Number and rest of box is information about serial number.

Now I need to conditional format all the picture boxes based on if the serial number is in either tables in a different sheet with a Y ticked in a cell next to it, I tried to use =OR(VLOOKUP($C$16,Sheet1'!$C$275:$N$294,12,0)="Y",VLOOKUP($C$16,Sheet1'!$C$119:$M$138,11,0)="Y") which I've encountered a couple of problems with:

• I can't copy the CF to the different boxes without manually adjusting the lookup value in the CF rule as the formula needs to stay fixed to cell pre copying over
• If it can't find the lookup in the second OR criteria then it returns an #N/A therefore not successfully CF'ing the cells even if it's true on the first OR criteria. My understanding of OR was that if one is true then it should be succesful?

Any help is greatly appreciated

Thanks

 

[Lewzerrrr]

I've solved the formula using an IFERROR instead of OR, still would like to address why OR doesn't work if it breaks due to an #N/A?