Conditionally format cell to turn red after lookup match

[Dan H.]

I have a blank column A and a second column B with different alpha-numeric
17-characters in cells B1 to B50000. These are vehicle VINs.
Now, when I enter a VIN in A1, I would like to find if this VIN is in
column B and, if it is, turn the A1 cell red.
Next, I would enter a new VIN in A2 and again, check to see if it is in
column B and, if so, turn A2 red. I would continue this data entry down in
column A for up to 700-1000 VINs. I'd expect maybe 3-4 matches after entering
about 1000 VINs in column A.

 

[Bob Phillips]

Use conditional formatting with a formula of

=ISNUMBER(MATCH(A1,B:B,0))

 

[Cordell]

Give this a try:

1. Highlight Column A
2. Format | Conditional Formating
3. In Condition 1, change to "Formula Is"
4. Enter =ISNUMBER(MATCH(A1,B:B,0)) as the formula
5. With the Format button, select the Patterns tab and use your Red Cell
shading

Hope this helps.

 

[Peo Sjoblom]

While it might be proper for pedagogical reasons when used for conditional
formatting the ISNUMBER part is not necessary. MATCH alone will suffice, it
seems like any number will be the equivalent of TRUE and an error would be
the equivalent of FALSE.

--


Regards,


Peo Sjoblom

 

[Dan H.]

Thanks folks! That worked perfectly.
In real life I'll have about 100,000 VINS in the lookup column.

 

[Bob Phillips]

I know that is so Peo, but I don't see it documented anywhere, so I would
rather be safe than trust MS to ensure it is always so