excel vba - Create macro to search Worksheet for list of strings and highlight the row -

excel vba - Create macro to search Worksheet for list of strings and highlight the row -

can help me create macro search excel worksheet list of 30 strings (e.g., sv-32488r1, sv-33485r1) , highlight row when found?

i using office 2010. i not excel or vba wiz have no thought start. the searches have found allow me search 1 string.

thank much in advance.

public sub highlightlistedvalues() dim strconcatlist string dim cell range 'creates string concatenating list of strings, separated |s 'e.g. "item1|item2|item3|item4|" each cell in sheets("list").range("a1:a30") strconcatlist = strconcatlist & cell.value & "|" next cell 'for each used cell in column of sheet1, check whether value in cell 'is contained within concatenated string each cell in intersect(sheets("sheet1").range("a:a"), sheets("sheet1").usedrange) if instr(strconcatlist, cell.value) > 0 'instr returns 0 if string isn't found cell.entirerow.interior.color = rgb(255, 0, 0) 'highlights row in reddish if value found end if next cell end sub

this highlight relevant rows in reddish if next true:

the info matching values searching in column of worksheet named "sheet1" your list of strings contained in cells a1:a30 of worksheet named "list"

amend names of worksheets , ranges needed e.g. if want search in columns c of worksheet named "data" amend sheets("sheet1").range("a:a") sheets("data").range("a:c")

hope helps!

excel-vba excel-2010 string-search