need help with finding a name in a row where all the names are separated by commas

[kevin lazell]

hi guys n gals
i would like some help with finding a name in a cell where all the names are separated by commas
i would prefer a vba solution to this please.
there will be two or more names separated by comma like thus john smith,joe bloggs,jeff cooling,peter smith, etc
the search will be done using first and last name
so shall we say the name to be searched for is in a1 and the names are in b2
if result =true then go and do something
if result =false then look for name in b3 then b4 etc
the rest of it i can do it is just this first bit i cant get my head around
thanks in advance

 

[kevin lazell]

thank you all that helped me i have decided to use fluffs code
just need to do some tweeking
thanks again most helpful

 

[Fluff]

Glad we could help & thanks for the feedback

 

[kevin lazell]

hi guys i have failed miserably to get this to work i have attatched a snap shot of my worksheet with intended result being the 3rd box
what i need code to do is lookup first name in cell a2 and check to see if it is in b2 if it is then check if interior color in c2 matches c1 it does not
so then checks d2 and d1 now this matches so j2=j2+1 now we go back and check if a2 is in b3 then a2 is in b4 etc then the code will start all over again
now checking 2nd name in cell a3 with cell b2 and b3 and b4 and so on
i think the main problem is in checking the name when using key f8 to step through the code and hover over fnd it displays the four names

Dim Fnd As Range
Dim a, x, name As Variant
a = 2: name = 2: x = 0
again:
x = x + 1

Set Fnd = Range("b:b").Find(Range("a" & name).Value, , , xlPart, , , False, , False)

If Fnd Is Nothing Then a = a + 1: GoTo again:






If Range("c" & a).Interior.ColorIndex = Range("c1").Interior.ColorIndex Then Range("i" & a) = Range("i" & a) + 1
If Range("d" & a).Interior.ColorIndex = Range("d1").Interior.ColorIndex Then Range("j" & a) = Range("j" & a) + 1
If Range("e" & a).Interior.ColorIndex = Range("e1").Interior.ColorIndex Then Range("k" & a) = Range("k" & a) + 1
If Range("f" & a).Interior.ColorIndex = Range("f1").Interior.ColorIndex Then Range("l" & a) = Range("l" & a) + 1
If Range("g" & a).Interior.ColorIndex = Range("g1").Interior.ColorIndex Then Range("m" & a) = Range("m" & a) + 1


a = a + 1
If x = 60 Then Exit Sub
If a = 12 Then a = 2: name = name + 1
GoTo again:
this is as far as i have got with the code a bit messy i know but just toying with it at the moment
any help will be greatly appreciated tia

 

[Fluff]

As this is now a totally different question, you need to start a new thread. Thanks