Excel - if a range of cells equals a value from a list I want to populate the value of another cell -

i trying write formula return value of 1 cell if range of cells equal value specific in list.

for example:

    a1 = orange     b1:b5 = data      if b1:b5 = red, blue, or green, display value a1 

thanks help!

dim range, listvalues() string 'array of string dim str string, integer   str = "red;blue;green" listvalues = split(str,";") 'split str using ";" i= lbound(listvalues) ubound(listvalues) 'loop thru array     set = activesheet.usedrange.find(what:=listvalues(i), lookat:=xlwhole)      if not nothing         'value found         a.value = range("a1").value     end if next 

i've adapted @davesexcel 's resolution, check one: how avoid using select in vba variable cell ranges?

this resolution rondebruin nice too:

http://www.rondebruin.nl/win/s9/win006.htm