I have a list of phrases in rows A1:A72. In A76:A642, I also a list of phrases that aren't exact matches of what's in A1:A72. What I'm trying to do is highlight the words in A1:A72 if there's match from A76:642. For example, A1 contains:
BOB WENT TO THE GROCERY STORE TODAY
A76 contains:
WENT STORE
I need only WENT STORE highlighted in A1.
This process needs to be repeated throughout A1:A72 by using what's in A76:A642
Works on the active sheet:
[vba]Sub HighlightWords()
'Create a dictionary of all the words in A76:A642 :
Set Dict = CreateObject("Scripting.Dictionary")
Dict.CompareMode = vbTextCompare
For Each cll In Range("A76:A642").Cells
myArray = Split(Application.Trim(cll.Value), " ")
For Each word In myArray
If Not Dict.Exists(word) Then Dict.Add word, word
Next word
Next cll
'Now check each word in each cell in A1:A72 :
For Each cll In Range("A1:A72")
myArray = Split(Application.Trim(cll.Value), " ")
For Each word In myArray
If Dict.Exists(word) Then
'highlight word:
cll.Characters(Start:=InStr(1, cll.Value, word, vbTextCompare), Length:=Len(word)).Font.ColorIndex = 3
End If
Next word
Next cll
End Sub
[/vba] but doesn't handle punctuation etc. - yet.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
This is great! I've been looking all over for some kind of help and I really appreciate this!
Does it need to handle punctuation etc.?
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
It doesn't. The phrases in the lower A column don't have punctuation and I've added this for the top part of the column to take care of the punctuation that appears there:
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Dim c As Range
For Each c In Selection.Cells
c = Replace(c, "'", " ")
c = Replace(c, ",", "")
c = Replace(c, "-", " ")
Thanks again for your help, I would have never figured this out.
My apologies, but I wasn't clear in my original post.
I got to looking at this for some targeted testing and it's not working quite how I need it to. For example, I have (A1:A2):
THE DOG IS BIG
THE CAT IS SMALL
In A4:A5, I have:
BIG THE
CAT SMALL
I'm expecting THE BIG to be highlighted in A1 (which is the case) and CAT SMALL in A2, but THE is also highlighted in A2.
If there's more than one word in the lower section of column A, in order for the words to be highlighted in the upper part of column A, all of the words must exist in that string. So the only way that THE CAT SMALL can be highlighted in A2 is if those words are present in one row (lower section). Of course, if there's a single word in the lower part of A, then any occurance of that word in the upper part of A should continue to be highlighted.
Can you help me out again?
Which is quite different…
What happens if, lt's take your example, in the upper section we have:
THE DOG IS BIG
and in the lower section we have a three single word entries:
IS
BIG
DOG
and nothing similar, what would you want highlighting in the upper section?
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
And is the sequence important?
ie should BIG DOG be highlighted if DOG BIG is in the lower section?
DOG IS BIG would be highlighted, but THE wouldn't be.Originally Posted by p45cal
Sequence isn't important. More examples (upper section):
DOG IS BIG
CAT IS SMALL
CAR IS BIG
BIKE IS SMALL
HOUSE IS PURPLE
HOUSE IS GREEN
lower section:
BIG DOG
PURPLE
HOUSE GREEN
This would be highlighted:
DOG IS BIG
CAT IS SMALL
CAR IS BIG
BIKE IS SMALL
HOUSE IS PURPLE
HOUSE IS GREEN
So, we have three words highlighted from 3 separate rows in the lower section.
What if, there were another row in the lower section which had
BIG DOG, now what would be highlighted in the same:
THE DOG IS BIG
my question being: would the IS be highlighted?
I think you'll say that the IS isn't highlighted.
If so, next scenario:
Upper section phrase:
BOB WENT TO THE GROCERY STORE TODAY
Lower section matches:
BOB WENT
GROCERY STORE
THE TODAY
what should be highlighted in the upper section phrase?
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
If BIG DOG is the only thing in the lower section, then you're correct, only DOG BIG would be highlighted. If we're still leaving in the initial three lower rows and adding BIG DOG, then DOG IS BIG would be highlighted. For the second question, words in bold would be highlighted:
BOB WENT TO THE GROCERY STORE TODAY
Understood. Sleep required. Tomorrow perhaps.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Note: made some corrections after initially posting.
[vba]Sub HighlightWords2()
For Each cll In Range("A1:A72")
'create an array of the words in cll:
'clean punctuation fist:
cllStr = UCase(cll.Value)
cllStr = Replace(cllStr, "'", " ")
cllStr = Replace(cllStr, ",", " ")
cllStr = Replace(cllStr, "'", " ")
cllList = Split(Application.Trim(cllStr), " ")
For Each cll2 In Range("A76:A642").Cells
cll2List = Split(Application.Trim(UCase(cll2.Value)), " ") 'assumes lower section has no puntuation.
'if all words in cll2List are in cllList then highlight those words in cll.
AllWordsFound = True ' (will set this to FALSE if any word not found)
For Each word2 In cll2List
WordFound = False '(will set this to TRUE if the word is found)
For Each word In cllList
If word = word2 Then
WordFound = True
Exit For 'no need to keep on looking so abort inner loop
End If
Next word
If Not WordFound Then
AllWordsFound = False
Exit For 'no need to keep on looking so abort outer loop
End If
Next word2
If AllWordsFound Then 'do the highlighting:
For Each word2 In cll2List
cll.Characters(Start:=InStr(1, cll.Value, word2, vbTextCompare), Length:=Len(word2)).Font.ColorIndex = 3
Next word2
End If
Next cll2
Next cll
End Sub
[/vba]
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
By golly, you did it! Thank you so much for all of your time working on this!
I don't think it works as it should
Why's that? I tested it and it works as I need it to. p45cal did a great job from my perspective.
…and?
(this means a bit of elaboration might be helpful)
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
I tried it and it works for the first line in the upper range, not for subsequent lines
And even for the first line it does not find the AND nor the I
(I changed the two ranges to A1:A9 and A10:A19)
I tried to put a picture in but it did not work
These are the test lines
BOB AND I WENT TO THE GROCERY STORE TODAY
JANE WENT FISHING TODAY
BOB AND I LIKE JANE
GROCERY STORE
THE TODAY
I WENT TOO
BOB AND I LIKE JANE
BOB WENT
So the first two lines are in the first range, the only words coloured for me are "BOB WENT THE GROCERY STORE TODAY" on the fist line, not the AND nor the I
And in the second line nothing was highlighted
Posting Permissions
Reply With Quote
