I will like highlight cells where duplicate are found in a range but if a word appears as part of group words in another cells, both cells should be highlighted. For example if my range is b1:b10 and cell b2= cat, cell b5= dog cat , cell b7= apple dog. Then all the cells should be highlighted because each one contains a text that can be found in another cell withing the range b1:b10.
i need it to colour the cell only if the occurrance of such word is greater than one. Also the word to look at is not limited to "dog" and "cat" but any words that appears more than once as dependent word or part of groups of words.


B1 ------ dog
B2 ------ book
B3 ------ cat
B4 ------ paper
B5 ------ bird ,dog
B6 ------ dog , chain
B7 ------ pen
B8 ------ cat
B9 ------ pencil

B1,B3,B5,B6,B8 will be highlighted and note that "pen" and "pencil" will not be highlighted as they are single words ( pencil is not separated by comma)

Any idea please