Find text and highlight in Excel cell

Hello all,

Suppose I have "abc" in A1, "def" in A2, "ghijk" in A3 and "lmn" in A4. I want to test a text string in A6 to see 1) the text string has any of A1/A2 and highlight as yellow; 2) test if the text has any of A3 and A4 and highlight as green. Let's say I have a text string in A6 like this, "abcghiabcdefghijkabcdeflmnabclmnlmn". Could someone help me with a macro?

Thanks,

MC

Oh, good insight. I need to spend more time in the pub

Here is code that will do that:

Please Login or Register  to view this content.

Thanks all for the response. It didn't go through at my hand...when debugging it said "unable to get the Characters property of the Range class" at the line "RefCell.Characters(Start:=Found, Length:=Len(CellToMatch)).Font.Color = ColorValue".
Any insight?

I tested this code in a sheet using your example data and it worked, see attached and compare yours to this. If you're having problems with the exact same code then it might help to attach the actual file you're working with. It is possible that there is a boundary condition with your data that the logic does not deal with properly.

I copied the one you attached and it worked very well. You are the man...

Glad to help! If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved.

Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."