What's the vlookup logic nd do you really want to VLookup 1000 & Marine?

Instead of testing for .ColorIndex = 15 (Gray in your sample WB) to ignore, could you test for xlColorIndexNone and process them i.e. the uncolored ones? Since there could be many shades of Gray, it might be safer