VBA to search Column, find string and Highlight

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

VBA to search Column, find string and Highlight

Post by bradjedis »

Greetings,

Looking for a VBA solution to search Column G, locate a specific string and highlight BOLD RED.

for sake of this question string of info is 123 ABC, actual string could be alpha, or alpha numeric.


Thanks,
Brad

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA to search Column, find string and Highlight

Post by HansV »

You could do this by using Conditional Formatting, but here is a VBA solution:

Code: Select all

Sub FindAndHighlight()
    Dim strFind As String
    Dim rng As Range
    Dim strAddress As String
    Application.ScreenUpdating = False
    strFind = "123 ABC"
    With Range("G:G")
        Set rng = .Find(What:=strFind, LookAt:=xlWhole, MatchCase:=False)
        If Not rng Is Nothing Then
            strAddress = rng.Address
            Do
                rng.Font.Bold = True
                rng.Font.Color = vbRed
                Set rng = .FindNext(After:=rng)
            Loop Until rng.Address = strAddress
        End If
    End With
    Application.ScreenUpdating = True
End Sub
As written, the code matches the entire cell. If you want partial matches, change xlWhole to xlPart.
And the match is case-insensitive. If you need case-sensitive matching, change MatchCase:=False to MatchCase:=True.
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: VBA to search Column, find string and Highlight

Post by bradjedis »

Well that worked pretty well, except I was hoping to highlight only the search string if found in the cell.

Thoughts?

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA to search Column, find string and Highlight

Post by HansV »

It is possible to do that, but it will make the macro VERY slow - processing individual characters in a cell is very time-consuming. Do you want it?
Best wishes,
Hans

User avatar
p45cal
2StarLounger
Posts: 142
Joined: 11 Jun 2012, 20:37

Re: VBA to search Column, find string and Highlight

Post by p45cal »

Test an adaptation of Hans' macro for speed:

Code: Select all

See next post for updated code
Last edited by p45cal on 10 Oct 2020, 17:12, edited 1 time in total.

User avatar
p45cal
2StarLounger
Posts: 142
Joined: 11 Jun 2012, 20:37

Re: VBA to search Column, find string and Highlight

Post by p45cal »

A few tweaks to match Hans' MatchCase:=False:

Code: Select all

Sub FindAndHighlight()
Dim strFind As String
Dim rng As Range
Dim strAddress As String
Application.ScreenUpdating = False
CaseSensitive = False    '<<adjust.
If CaseSensitive Then CompareType = vbBinaryCompare Else CompareType = vbTextCompare
strFind = "123 aBC"
myLen = Len(strFind)
With Range("G:G")
  Set rng = .Find(What:=strFind, LookAt:=xlPart, MatchCase:=CaseSensitive)
  If Not rng Is Nothing Then
    strAddress = rng.Address
    Do
      Posn = InStr(1, rng.Value, strFind, CompareType)
      Do
        With rng.Characters(Start:=Posn, Length:=myLen).Font
          .FontStyle = "Bold"
          .Color = vbRed
        End With
        Posn = InStr(Posn + 1, rng.Value, strFind, CompareType)
      Loop Until Posn = 0
      Set rng = .FindNext(After:=rng)
    Loop Until rng.Address = strAddress
  End If
End With
Application.ScreenUpdating = True
End Sub

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: VBA to search Column, find string and Highlight

Post by bradjedis »

Thanks all. I am just getting back this project. I will test on Friday!

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: VBA to search Column, find string and Highlight

Post by bradjedis »

That did the trick! and quite quick. processed 14K rows, with some cells being quite full..


Thanks!

sachin483
2StarLounger
Posts: 101
Joined: 03 Feb 2018, 04:20

Re: VBA to search Column, find string and Highlight

Post by sachin483 »

after finding the string can it be replaced and highlighted the same,and how to add more color option instead of RED

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA to search Column, find string and Highlight

Post by HansV »

You can change vbRed to any other color of course.

What do you mean by "can it be replaced and highlighted the same"? What do you want to replace the string with?
Best wishes,
Hans

sachin483
2StarLounger
Posts: 101
Joined: 03 Feb 2018, 04:20

Re: VBA to search Column, find string and Highlight

Post by sachin483 »

Eg we are finding 123 aBC in the string and then I want to replace with abc123 and then highlighting the string which is replaced

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA to search Column, find string and Highlight

Post by HansV »

Here is a new version, based on p45cal's macro:

Code: Select all

Sub FindAndHighlight()
    Const strFind = "123 ABC"
    Const strReplace = "Tadaa"
    Const CaseSensitive = False
    Dim CompareType As VbCompareMethod
    Dim lngFind As Long
    Dim lngReplace As Long
    Dim lngPos As Long
    Dim rng As Range
    Dim strAddress As String

    Application.ScreenUpdating = False
    If CaseSensitive Then
        CompareType = vbBinaryCompare
    Else
        CompareType = vbTextCompare
    End If
    lngFind = Len(strFind)
    lngReplace = Len(strReplace)
    With Range("G:G")
        Set rng = .Find(What:=strFind, LookAt:=xlPart, MatchCase:=CaseSensitive)
        If Not rng Is Nothing Then
            strAddress = rng.Address
            Do
                lngPos = InStr(1, rng.Value, strFind, CompareType)
                Do
                    rng.Characters(Start:=lngPos, Length:=lngFind).Text = strReplace
                    With rng.Characters(Start:=lngPos, Length:=lngReplace).Font
                        .Bold = True
                        .Color = vbGreen
                    End With
                    lngPos = InStr(lngPos + lngReplace, rng.Value, strFind, CompareType)
                Loop Until lngPos = 0
                Set rng = .FindNext(After:=rng)
                If rng Is Nothing Then Exit Do
            Loop Until rng.Address = strAddress
        End If
    End With
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

sachin483
2StarLounger
Posts: 101
Joined: 03 Feb 2018, 04:20

Re: VBA to search Column, find string and Highlight

Post by sachin483 »

thanks a lot working fine

sachin483
2StarLounger
Posts: 101
Joined: 03 Feb 2018, 04:20

Re: VBA to search Column, find string and Highlight

Post by sachin483 »

i have change the range from - With Range("G:G") to --With Range("C:I") and i am getting following error

Run time error '1004'
Unable to set text property of the Character class

debug at line

rng.Characters(Start:=lngPos, Length:=lngFind).Text = strReplace

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA to search Column, find string and Highlight

Post by HansV »

Could you attach a sample workbook that demonstrates the error?
Best wishes,
Hans