Is there a macro/add-in to allow partial text filtering using one column to filter another?

[indian]

How do you write a macro, script or conditional formatting (or is there an add-in I can buy) that will use a partial text match from cells in one column (column J), to filter the list of phrases in another column (Column A). I found a formula that lets me filter column A by just one cell in column J, but I want it to check each cell/word or text I have (about 50 cells in column J).<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>In my situation, column A has over 1,000 rows of text phrases. Column J contains text or words that I want to use to filter column A with, such that if a cell in Column A contains the text/words in any of the cells in column J, the cells in column A will be highlighted (or take the same formatting I have set on the cells in column J). Basically I am using this to sort cells that contain words I do not want in my column A list.<o></o>
I have been doing this manually (Excel 2010 64bit) by using the replace button, going from J2 cell copy/paste the word in that cell in the find/replace area, and replace it with the same word, I only change the formatting so that I can later sort by color. The problem with this approach is I have 50 or so cells on each worksheet in column J to run through so this takes quite a bit of time for each sheet I have.<o></o>
Example:<o></o>
Column A
xyz power tools
xyz warranty
xyz warranty repair
xyz used parts
xyz part
xyz stainless steel appliance
xyz wood appliance
freestanding xyz
xyz troubleshooting
apartment size xyz<o></o>
Column J:
warranty
tool
part
parts
trouble
repair<o></o>
---------------
So notice a couple things, I have filtering word in column J “part “ (with a space after it) and parts, thus anything with parts or a part I want to highlight, though I want apartment to not be highlighted, when manually doing this, I use the find/replace and add a space after the word part. Is it possible to filter this when using a formula or code/macro?<o></o>
I am wanting to highlight matches for partial matches like “trouble” will highlight cells in Column A that include the words “troubles” or “troubleshooting”.<o></o>
Is it possible to have the formula/code to have the cell formatting on Column A match that of Column J, (if the cell Column A contains the text/word in column J) and that it goes through the list as I would, from the top down. <o></o>
I need to do this on several different worksheets/workbooks in Excel. What is the best way to simplify this process? Is there a macro, coding or even an add-in I could buy that I can use to keep me from having to do this manually? <o></o>

 

[indian]

Luke in another forum /website resolved this. Creating a Macro with the below, where the J100 must be the last cell in the column J that is populated with the filtering word. If you need more, then you must edit this code to relect the additional cell. When I first ran it stopped with an error as I didn't have 100 cells in J. Changing this to the lower amount I had allowed this to run without error.

Sub MatchFormats()
'Define range with words you want to find
For Each c1 In Range("J1:J100")
'Define range with words you want to search
For Each c2 In Range("A1:A1000")
'Checks is word is contained in cell
If c2 Like "*" & c1 & "*" Then
'If match, set pattern color to match
c2.Interior.ColorIndex = c1.Interior.ColorIndex
End If
Next c2
Next c1
End Sub