VBA to find text string in column

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
149
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Been trying to find a simple VBA code to find a phrase in a column and highlight all instances. I don't want popup boxes or find from one column and highlight in another column.
Example: Find "A cow/dog jumps over the moon" in column D.
Highlight cell in pink.
Can I highlight text also?
I tried to use conditional formatting but it didn't work at all. So prefer to just use VBA code.
Thank you.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,973
Office Version
  1. 2016
Platform
  1. Windows
In the message that I posted my first code in I said I was assuming the text to find would be entered into cell A1.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,973
Office Version
  1. 2016
Platform
  1. Windows
A1 has something else in there.
Here is what I wrote in my first message...
"Anyway, I had to make an assumption because you did not tell us... I assume the text
you want to search for is in cell A1 and that the column you want to search is Column D."
If you don't tells us what you want or need, then we can either ignore your question or take a guess assuming you will figure out how to change it... I took a guess at cell A1 (remember, I cannot see your worksheet so I do not know what you have in which cells. I'm still not 100% sure how you want to specify the text to search, but from what you wrote earlier I am assuming you want to specify it directly in the code. Given that, here is my code modified to do that where I give you a place to specify the text to search for and the column to search in...
VBA Code:
Sub HighlightText()
  Dim Pos As Long, StartAt As String, Cell As Range
  Dim TextToFind As String, ColumnLetterToSearch As String
  
  TextToFind = "A cow/dog jumps over the moon"
  ColumnLetterToSearch = "D"
  
  Application.ScreenUpdating = False
  With Columns(ColumnLetterToSearch)
    .ClearFormats
    .Font.ColorIndex = xlNone
    .Font.Bold = False
    Set Cell = .Find(TextToFind, , , xlPart, , xlNext, False, , False)
    If Not Cell Is Nothing Then
      StartAt = Cell.Address
      Do
        Cell.Interior.Color = RGB(255, 100, 50)
        Pos = InStr(1, Cell.Value, TextToFind, vbTextCompare)
        With Cell.Characters(Pos, Len(TextToFind))
          .Font.Color = vbWhite
          .Font.Bold = True
        End With
        Set Cell = .FindNext(Cell)
      Loop While Not Cell Is Nothing And Cell.Address <> StartAt
    End If
  End With
  Application.ScreenUpdating = True
End Sub
 
Solution

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
149
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Here is what I wrote in my first message...

If you don't tells us what you want or need, then we can either ignore your question or take a guess assuming you will figure out how to change it... I took a guess at cell A1 (remember, I cannot see your worksheet so I do not know what you have in which cells. I'm still not 100% sure how you want to specify the text to search, but from what you wrote earlier I am assuming you want to specify it directly in the code. Given that, here is my code modified to do that where I give you a place to specify the text to search for and the column to search in...
VBA Code:
Sub HighlightText()
  Dim Pos As Long, StartAt As String, Cell As Range
  Dim TextToFind As String, ColumnLetterToSearch As String
 
  TextToFind = "A cow/dog jumps over the moon"
  ColumnLetterToSearch = "D"
 
  Application.ScreenUpdating = False
  With Columns(ColumnLetterToSearch)
    .ClearFormats
    .Font.ColorIndex = xlNone
    .Font.Bold = False
    Set Cell = .Find(TextToFind, , , xlPart, , xlNext, False, , False)
    If Not Cell Is Nothing Then
      StartAt = Cell.Address
      Do
        Cell.Interior.Color = RGB(255, 100, 50)
        Pos = InStr(1, Cell.Value, TextToFind, vbTextCompare)
        With Cell.Characters(Pos, Len(TextToFind))
          .Font.Color = vbWhite
          .Font.Bold = True
        End With
        Set Cell = .FindNext(Cell)
      Loop While Not Cell Is Nothing And Cell.Address <> StartAt
    End If
  End With
  Application.ScreenUpdating = True
End Sub
My sincerest apologies if I didn't state my requirements clear enough.
Thank you Rick Rothstein. You're awesome! Enjoy your evening.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,549
Messages
5,636,929
Members
416,953
Latest member
prakashkumar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top