Do you have to do it with VBA code, or are you assuming it can't be
done otherwise? Without VBA, just create the one conditional format
with the CELL VALUE IS EQUAL TO:

VLOOKUP(<currentCell,B2:B6,1,false)

If the value of the current cell is found in the range, the selected
formatting will be applied.

Mike Anas
http://mikeanas.googlepages.com/