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-com
ffice
ffice" /><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
>
![Eek! :o :o](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f631.png)
![Eek! :o :o](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f631.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
Example:<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
Column J:
warranty
tool
part
parts
trouble
repair<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
---------------
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)