• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I Highlight cells in 1 column that contain text that are in another colmn

G

GF2011

New Member
Here's the scenario:

Column A has over 1,000 rows of text phrases. Column J contains text that I want to use to compare against column A, such that if a cell in Column A contains the text 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).


I have been doing this manually (Excel 2010 64bit) by using the replace button, going from J1 cell copy/paste as my word to find/replace and replace it with the same word, I only change the formating 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.


Example:


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


Column J:

warranty (note this has neutral formatting on it)

tool (this and the below use "bad" label/formatting on them)

part

parts

trouble

repair


---------------

So notice a couple things, I have part (with a space after it)and parts, thus anything with parts or a part I want to be 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.


The next thing is want to it to highlight matches for partial matches like trouble will highlight trouble or troubleshooting.


It would be nice if the formatting done on Column A matches that of Column J, and that it goes through the list as I would, from the top down. This methodology would allow me to classify a cell in column A that contains "xyz manual" and "xyz warranty repair" with its yellow/neutral highlight, but as it goes down the list and because the latter term includes the word "repair", it will end up with the "bad" (or red) formatting.


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 or something I can use to keep me from having to do this manually?
 
This a very basic macro, which compares words from 1 range with a set of cells in another range. Note that your one situtaion of wanting to highlight "xyz part" but not "apartment" will not be covered (I'm not sure how you would do that without adding much more code to check for spaces/no leading spaces, etc). The code does go in order down the list, as you requested. Again, it's rudimentary, but hopefully it gives some guidance or gives someone else an idea.


Code: 
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
 
Thanks Luke M.

This is my first time to use a macro, but I unhide the hidden workbook, "recorded a macro" (doing nothing and then saving it -didn't know how else get the information in).

I then editted the Macro (removing everything and pasted your code). I ran the macro and saw some items quickly highlight in Column A, but the highlight dissapeared in less than a second. Here is what I pasted:

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


--------------

Should I have done something differently?


Thanks
 
You'll need to manually define the 2 ranges stated at the beginning of the macro to match your scenario. For instance, if your list of words in column J is really just J2:J30, change the code line to:


Code: 
For Each c1 In Range("J2:J30")
 
For readers of this. Luke's solution was exactly what I needed. Further, to add the ability to filter off the unwanted xyz product kits, or xyz product kit. By putting two cells in the J column, one with kits and the other with kit (where I put a space after the word kit. This allows the filter process to highlight kits, and xyz kit, but not highlight items such as kitchen.


Thnks again Luke.
 
This has been working great, but I ran into two worksheets this morning that stopped the macro. When going into the debug mode, the following line is highlighted:

If c2 Like "*" & c1 & "*" Then


What do I need to do to resolve? (what is causing the error?)


Thanks!
 
Hmm. Any idea what the current value of c2 and c1 is? You can usually hover the mouse over the constant and see what value is currently stored. Are there any special characters in your list (exclamation marks, brackets, hyphen)?
 
Thanks Luke. That was an odd one. The two sheets were "corrupt" what was causing the issue was down around cell 800 or so of the A column was a name? ... some how an = sign was in front of the name.


Thanks again.
 
You must log in or register to reply here.
Back
Top