excel - How to check if a a range of strings in a column, are contained in another column in another worksheet?

Question

I would like to check a range of strings using a macro

(example of strings: string sometimes contains numbers: VNVININT2HAI, BRKARLTD999FRA)

in a column (H3:H100) in Sheet1 and if it exists in a column(K2:K100) in Sheet2

then mark the cells in Sheet1 either green if the string matches up with any of the strings in the column in Sheet2. Then Mark the Cells in Sheet1 either green if it matches or red if it doesnt.

I've googled for answers found mostly code for integers, and formulas using Vlookup, not the exact answer I was looking for.

Answer 1

You have multiple questions. However I'll answer part of it by saying COUNTIF is an underrated tool for CHECKING if something exists or not. Countif is desirable because it can check cells without throwing errors as VLookup or Match might.

Example for checking if value exists in a range could be written like this in excel =OR(COUNTIF(E:E,"VNVININT2HAI")) which would return True if any cell had a value of your text.

You can also use it to check if a cell contains it by using * for the sides.

Example: =OR(COUNTIF(E6,"*2*")) would return True or False depending if a cell contains a "2" anywhere in it.

I doubt this fully answers your question, but for people looking on how to "check cells and ranges" COUNTIF is probably more useful than you think. It's applicable in VBA too (sometimes I prefer it over instr).

EDIT This works, but if you have a LOT of cells to check, perhaps consider using IsNumber(Match() as shown in comments by @scottCraner