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
>




<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


Example:<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


Column J:
warranty
tool
part
parts
trouble
repair<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


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


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


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

