VBA to find text string in column

[NeoSez]

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]

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.

 

[NeoSez]

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.

A1 has something else in there.

 

[Rick Rothstein]

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...

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

 

[NeoSez]

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...

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.