Find Colum A Text and if this text in Colum A cells contains in Coumn B then Highlight either Colum A or Colum B

[kumail]

In Excell i have one word in each cell of Colum A, I have Multiple words in each cell of Column B. I want to identify if colum A Cells text is contained in Column B Cells then either highlight Particular cells of colum A or colum B so that i can identify those cells in which Colum A one word cell is present in Colum B multiple words cell. if there is any formula through which i can highlight particular cells of Either Column A or Colum B if colum A one word Text is conatins in colum B multiple word text. Thanks and awaiting for response
incase if could not make my query clear i further explain that in colum A we have title of shope names only. and in colum B we have complete shop address alongwith shop name. i want to identify or highlight the cells of same stores in column A and Colum B. Please Help

 

[Peter_SSs]

Welcome to the MrExcel board!

Would this conditional formatting work for you?

23 02 25.xlsm
	A	B
1	cat	five cats
2	dog	mouse and rat
3	horse	cow and pig
4	pig
5		the dog chased the cat
6		the sheep scattered
7
8
9
10
CF Matching Words

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B10	Expression	=AND(B1<>"",COUNT(SEARCH(" "&A$1:A$10&" "," "&B1&" ")))	text	NO
A1:A10	Expression	=AND(A1<>"",MATCH("* "&A1&" *"," "&B$1:B$10&" ",0))	text	NO

 

[kumail]

Welcome to the MrExcel board!

Would this conditional formatting work for you?

23 02 25.xlsm
	A	B
1	cat	five cats
2	dog	mouse and rat
3	horse	cow and pig
4	pig
5		the dog chased the cat
6		the sheep scattered
7
8
9
10
CF Matching Words

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B10	Expression	=AND(B1<>"",COUNT(SEARCH(" "&A$1:A$10&" "," "&B1&" ")))	text	NO
A1:A10	Expression	=AND(A1<>"",MATCH("* "&A1&" *"," "&B$1:B$10&" ",0))	text	NO

Thanks Peter for your help. just one last thing could you please tell me where to put these formulas (=AND(B1<>"",COUNT(SEARCH(" "&A$1:A$10&" "," "&B1&" ")))) in conditional formating in New Rule or where. Once again i am very thanksful for your kind help that exactly what i need the image you shown containing cat dog and pig in the image. thanks

 

[Peter_SSs]

could you please tell me where to put these formulas

For your sample data in the post #1 image

• Select A1:A8 (or a larger range if more data might be added later)
• Home ribbon tab -> Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =AND(A1<>"",MATCH("* "&A1&" *"," "&B$1:B$25&" ",0)) -> Format... -> Fill tab -> Choose your colour-> OK -> OK
  If you have, or might later have, more data in column B than to row 25 then make that red range in the formula larger, it won't hurt.
  
  
• Now select B1:25 (or a larger range if more data might be added later)
• Home ribbon tab -> Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =AND(B1<>"",COUNT(SEARCH(" "&A$1:A$8&" "," "&B1&" "))) -> Format... -> Fill tab -> Choose your colour -> OK -> OK
  If you have, or might later have, more data in column A than to row 8 then make that blue range in the formula larger, it won't hurt.