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]

Will the text you want to find be in a cell all by itself or will, or could, there be other text in the cell with it?

 

[NeoSez]

Will the text you want to find be in a cell all by itself or will, or could, there be other text in the cell with it?

Hi Rick. Thanks for looking into my issue.
The text will be found anywhere amongst other text in the cell.

 

[Rick Rothstein]

Okay, coloring the cells is easy, so I'll give you the code for that, but I'm about to go to sleep and highlighting the text itself will take more time than I have (it involves looping, searching, selecting, coloring) so I'll leave that to another volunteer for the rest of the night. 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. With that said, here is the macro (no loop needed)...

Sub HighlightText()
  Columns("D").ClearFormats
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.ColorIndex = 38
  Columns("D").Replace Range("A1").Value, "", xlPart, , False, , False, True
  Application.ReplaceFormat.Clear
End Sub

 

[NeoSez]

Okay, coloring the cells is easy, so I'll give you the code for that, but I'm about to go to sleep and highlighting the text itself will take more time than I have (it involves looping, searching, selecting, coloring) so I'll leave that to another volunteer for the rest of the night. 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. With that said, here is the macro (no loop needed)...

Sub HighlightText()
  Columns("D").ClearFormats
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.ColorIndex = 38
  Columns("D").Replace Range("A1").Value, "", xlPart, , False, , False, True
  Application.ReplaceFormat.Clear
End Sub

 

[NeoSez]

Rick Rothstein: The text to search and highlight is all in the same column D.
"Find "A cow/dog jumps over the moon" in column D"

Can this be inserted in there somewhere?

                  c.Interior.Color = RGB(255, 100, 50)
                    Do
                        fn.Interior.Color = RGB(255, 100, 50)

 

[Rick Rothstein]

Can this be inserted in there somewhere?

                  c.Interior.Color = RGB(255, 100, 50)
                    Do
                        fn.Interior.Color = RGB(255, 100, 50)

All we had to do is change the assignment from ColorIndex to Color...

Sub HighlightText()
  Columns("D").ClearFormats
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior..Color = RGB(255, 100, 50)
  Columns("D").Replace Range("A1").Value, "", xlPart, , False, , False, True
  Application.ReplaceFormat.Clear
End Sub

Question about the highlighting of the text that you asked about... did you want all the text in the cell colored or only the text being searched for? Also, what color did you want the text to be?

 

[NeoSez]

All we had to do is change the assignment from ColorIndex to Color...

Sub HighlightText()
  Columns("D").ClearFormats
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior..Color = RGB(255, 100, 50)
  Columns("D").Replace Range("A1").Value, "", xlPart, , False, , False, True
  Application.ReplaceFormat.Clear
End Sub

Question about the highlighting of the text that you asked about... did you want all the text in the cell colored or only the text being searched for? Also, what color did you want the text to be?

Hey Rick. Thanks for your quick response.
I only want that searched text string to be coloured. Colour can be red. That's fine. If I want to change it later, I can try to figure it out on my own. If I can't, I will come back to ask

 

[Rick Rothstein]

I only want that searched text string to be coloured.

Replace the code I gave you earlier with this instead...

Sub HighlightText()
  Dim Pos As Long, StartAt As String, Cell As Range
  With Columns("D")
    Set Cell = .Find(Range("A1").Value, , , 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, Range("A1").Value, vbTextCompare)
        With Cell.Characters(Pos, Len(Range("A1").Value))
          .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
End Sub

Note: Red font did not show up against the color you gave us for the cell fill color, so I made the font white and bold instead.

 

[NeoSez]

Replace the code I gave you earlier with this instead...

Sub HighlightText()
  Dim Pos As Long, StartAt As String, Cell As Range
  With Columns("D")
    Set Cell = .Find(Range("A1").Value, , , 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, Range("A1").Value, vbTextCompare)
        With Cell.Characters(Pos, Len(Range("A1").Value))
          .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
End Sub

Note: Red font did not show up against the color you gave us for the cell fill color, so I made the font white and bold instead.

Thanks Rick Rothstein
Does this line need to be added in there to tell it what to find? There are other text in the same cell.

If cell.value="A cow/dog jumps over the moon" then