VBA - Highlight specific key words

[Cruiser69]

Hi all.

I use the following code to highlight in red, certain keywords which appear in a sheet.
There are many more, but I have just shortened it for this purpose.
It works well, but it will highlight anything which contains the word e.g TEA
Like STEAM MOP.
Or BOOKCASE and not just BOOK

Is there a way to just highlight the specific word I have in the Comments

Dim ws As Worksheet

Dim Match As Range

Dim Comment() As String

Set ws = ActiveWorkbook.Worksheets(1)

ReDim Comment(3)

Comment(0) = "TEA"

Comment(1) = "COFFEE"

Comment(2) = "BOOK"



For i = LBound(Comment) To UBound(Comment)

Set Match = ws.Cells.Find(What:=Comment(i), LookIn:=xlValues, _

LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False)



If Not Match Is Nothing Then

FirstAddress = Match.Address

Do

sPos = InStr(1, Match.Value, Comment(i))

sLen = Len(Comment(i))

Match.Characters(Start:=sPos, Length:=sLen).Font.Color = RGB(255, 0, 0) ‘Red

Match.Interior.Color = RGB(153, 255, 153) 'Green

Set Match = ws.Cells.FindNext(Match)

Loop While Not Match Is Nothing And Match.Address <> FirstAddress

End If

Next

Thanks for looking

Regards,

Graham

 

[NdNoviceHlp]

Maybe...

LookAt:=xlWhole

and/or...

If (Not Match Is Nothing) And (Len(Match) = Len(Comment(i)) Then

HTH. Dave

 

[Cruiser69]

Hi Dave

The first code did not highlight any words.

The second came up with Compile error Expected: )

Regards,

Graham

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Highlight specific key words
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[NdNoviceHlp]

Whoops. Missed a bracket...

If (Not Match Is Nothing) And (Len(Match) = Len(Comment(i))) Then

Dave

 

[Cruiser69]

Hi Fluff.

I wasn't aware of that.
The link below is where I posted the same question

VBA Highlight specific key words

Hi all. I use the following code to highlight in red, certain keywords which appear in a sheet. There are many more, but I have just shortened it for this purpose. It works well, but it will highlight anything which contains the word e.g. TEA Like STEAM MOP. Or BOOKCASE and not just BOOK...

www.excelforum.com

 

[Cruiser69]

Hi Dave

I've tried both and can't get them to work

 

[NdNoviceHlp]

Here's what code I would use. Seems to work fairly fast and only highlights the exact word. Dave

Sub Test()
Dim ws As Worksheet
Dim Rng As Range
Dim Comment As Variant

Set ws = ActiveWorkbook.Worksheets(1)
Comment = Array("TEA", "COFFEE", "BOOK")
On Error GoTo ErFix
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual

For Each Rng In ws.UsedRange
If Rng.Text <> vbNullString Then
For i = LBound(Comment) To UBound(Comment)
If LCase(Rng.Text) = LCase(Comment(i)) Then
Rng.Interior.Color = RGB(153, 255, 153) 'Green
End If
Next i
End If
Next Rng
ErFix:
If Err.Number <> 0 Then
MsgBox "Error"
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlAutomatic
End Sub

 

[Cruiser69]

Hi Dave.

I've tried it but can't get it to work. Does it make a difference if the text is in upper or lower case. It will always be in uppercase.

 

[NdNoviceHlp]

Hmmm. I trialed the code with success before posting? It doesn't matter upper or lowercase. The LCase in the comparative test is used just to compare apples to apples so that case changes between the compared strings doesn't matter. When U say can't get it to work, what does that mean? It errors? Does nothing? Wrong output? Depending on the size of your sheet's used range, it may take some time to run. Dave