Marking a cell in a lookup table

[dugmick]

Good afternoon -

I have a workbook sheet1 that has a column (A) of defective serial numbers and column B which has "NG" in its cells, this is my lookup table. Sheet2 contains a column (K) populated from a barcode scanner, with an adjacent column (L) performing a vlookup to column A in sheet1. If a match is found, it returns the "NG" to the cell in column L adjacent to the the last scanned barcode and I turn the cell red; if no match is found I turn the cell green and label it with an "OK".

I would like to fill the cells in the lookup table (sheet1 column A) with yellow if they matched the scan results, so at the end of the day I can see what numbers are matched and what numbers are not yet matched. I am weak with VBA, what little bit I have added is from trial and error, with a healthy dose of Google results for inspiration.

Thanks for looking and for any suggestions!

 

[C Moore]

Conditional formatting will do it. Select values in sheet1 column A, then Conditional Formatting, New Rule, Use Formula.
=ISNUMBER(MATCH(A1,$K$1:$K$(max),0))
This will return TRUE if it exists, set format fill to yellow.
A1 assumes its the active cell in the range selected, adjust if needed.

 

[dugmick]

Conditional formatting will do it. Select values in sheet1 column A, then Conditional Formatting, New Rule, Use Formula.
=ISNUMBER(MATCH(A1,$K$1:$K$(max),0))
This will return TRUE if it exists, set format fill to yellow.
A1 assumes its the active cell in the range selected, adjust if needed.

Thanks for the reply. I realized I didn't add to the original post that at the end of each day, the workbook is copied to a network drive and the original has the cells cleared with that day's results. I believe the conditional formatting would then see no matches, and all of the cells in the lookup sheet would revert back to no fill.