VBA to find text string in column

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
146
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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,882
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,882
Office Version
  1. 2010
Platform
  1. Windows
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)...
VBA Code:
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

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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)...
VBA Code:
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

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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?
VBA Code:
                  c.Interior.Color = RGB(255, 100, 50)
                    Do
                        fn.Interior.Color = RGB(255, 100, 50)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,882
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Can this be inserted in there somewhere?
VBA Code:
                  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...
VBA Code:
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?
 
Last edited:

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
All we had to do is change the assignment from ColorIndex to Color...
VBA Code:
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:biggrin:
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,882
Office Version
  1. 2010
Platform
  1. Windows
I only want that searched text string to be coloured.
Replace the code I gave you earlier with this instead...
VBA Code:
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.
 
Last edited:

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Replace the code I gave you earlier with this instead...
VBA Code:
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.
VBA Code:
If cell.value="A cow/dog jumps over the moon" then
 

Watch MrExcel Video

Forum statistics

Threads
1,127,751
Messages
5,626,654
Members
416,198
Latest member
Enigma909

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