Highlight exact text in cell

[mjcanty]

I know this question has been answered in a previous post; however, it appears that the VBA highlighted the text if any part of the word appeared (i.e. "blue" was highlighted if it was found as 'blue' or 'bluebird'). What I am trying to accomplish is to have a macro find and highlight only when "EWS" is found in any cell contained in columns AU and AV. The search term "EWS" is located in cell AX1. I used the macro below; however, it is highlighting any string that contains "EWS," including "news" "newspaper" etc. How can this be altered where it will only highlight when "EWS" is in the cell? Also, how do I incorporate column AV? The data begins in cell AU1927.

Sub Test1()
Dim strString$, x&
Dim rngCell As Range

strString = Range("AX1").Value
Application.ScreenUpdating = False
For Each rngCell In Range("AU1927", Range("AU" & Rows.Count).End(xlUp))
With rngCell
.Font.ColorIndex = 1
For x = 1 To Len(.Text) - Len(strString) Step 1
If Mid(.Text, x, Len(strString)) = strString Then .Characters(x, Len(strString)).Font.ColorIndex = 5
Next x
End With
Next rngCell
Application.ScreenUpdating = True
End Sub

 

[JackDanIce]

Do you mean you want to only change font colour of cells where the text exactly matches AX1? In other words only cells that contain the value "EWS"? It's not clear why you're looping over each string to find matching parts if that isn't what you want..

 

[mjcanty]

My apologies. The value in cell AX1 is "EWS". There are strings of text in the cells of columns AU and AV. What I would like to have happen is whenever the exact text of "EWS" appears within the string of that particular cell, I would like "EWS" highlighted; however, if "news" or "newspaper" is within the string, I do not want it highlighted.

 

[Rick Rothstein]

My apologies. The value in cell AX1 is "EWS". There are strings of text in the cells of columns AU and AV. What I would like to have happen is whenever the exact text of "EWS" appears within the string of that particular cell, I would like "EWS" highlighted; however, if "news" or "newspaper" is within the string, I do not want it highlighted.

Does this do what you want...

[table="width: 500"]
[tr]
	[td]Sub HighlightExactText()
  Dim X As Long, strString, rngCell As Range
  strString = Range("AX1").Value
  Application.ScreenUpdating = False
  For Each rngCell In Range("AU1927:AV" & Columns("AU:AV").Find("*", , xlValues, , xlRows, xlPrevious).Row)
    With rngCell
      .Font.ColorIndex = 1
      If InStr(1, .Text, strString, vbTextCompare) Then
        For X = 1 To Len(.Text) - Len(strString) + 1
          If Mid(" " & UCase(.Text) & " ", X, Len(strString) + 2) Like "[!A-Z0-9]" & UCase(strString) & "[!A-Z0-9]" Then .Characters(X, Len(strString)).Font.ColorIndex = 5
        Next
      End If
    End With
  Next
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[mjcanty]

Rick,
This works perfectly! Thank you very much. I decided to change the font color to red because I think it stands out better. I tried to have it also turn bold, but it didn't work for me. What do I need to add so the text changes color and is bold?

 

[Rick Rothstein]

Rick,
This works perfectly! Thank you very much. I decided to change the font color to red because I think it stands out better. I tried to have it also turn bold, but it didn't work for me. What do I need to add so the text changes color and is bold?

Try using this code instead of what I gave you earlier...

[table="width: 500"]
[tr]
	[td]Sub HighlightExactText()
  Dim X As Long, strString, rngCell As Range
  strString = Range("AX1").Value
  Application.ScreenUpdating = False
  For Each rngCell In Range("AU1927:AV" & Columns("AU:AV").Find("*", , xlValues, , xlRows, xlPrevious).Row)
    With rngCell
      .Font.ColorIndex = 1
      If InStr(1, .Text, strString, vbTextCompare) Then
        For X = 1 To Len(.Text) - Len(strString) + 1
          If Mid(" " & UCase(.Text) & " ", X, Len(strString) + 2) Like "[!A-Z0-9]" & UCase(strString) & "[!A-Z0-9]" Then
            .Characters(X, Len(strString)).Font.ColorIndex = 3
            .Characters(X, Len(strString)).Font.Bold = True
          End If
        Next
      End If
    End With
  Next
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[mjcanty]

[COLOR=#333333][FONT=monospace].Characters(X, Len(strString)).Font.Bold = True[/FONT][/COLOR]

That is exactly the line I entered to make the font bold; however, I must have inadvertently deleted the

End If

because when I tried to run the macro, I was getting an error.

Thanks!

 

[Rick Rothstein]

That is exactly the line I entered to make the font bold; however, I must have inadvertently deleted the
[CODE]End If

because when I tried to run the macro, I was getting an error.

No, you did not inadvertently delete the "End If".... there was none in my original code. The "If..Then" statement I used to color the text was the one-line version of "If..Then" where the single action statement was placed immediately after the "Then" keyword... doing it that way means no "End..If" statement is used. Adding the bolding statement meant the "If..Then" now had two action statements to perform, so the one line version of "If..Then" was no longer applicable, so I moved the color statement from after the "Then" keyword down to the next line, added the bolding statement after it and then had to add the "End..If" statement in order to close off the block form of the "If..Then" statement that had now been created.

 

[mjcanty]

Now I see what you mean. Thanks, Rick!