VBA to search Column, find string and Highlight
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
VBA to search Column, find string and Highlight
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
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
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA to search Column, find string and Highlight
You could do this by using Conditional Formatting, but here is a VBA solution:
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.
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
And the match is case-insensitive. If you need case-sensitive matching, change MatchCase:=False to MatchCase:=True.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: VBA to search Column, find string and Highlight
Well that worked pretty well, except I was hoping to highlight only the search string if found in the cell.
Thoughts?
Thoughts?
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA to search Column, find string and Highlight
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
Hans
-
- 2StarLounger
- Posts: 146
- Joined: 11 Jun 2012, 20:37
Re: VBA to search Column, find string and Highlight
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.
-
- 2StarLounger
- Posts: 146
- Joined: 11 Jun 2012, 20:37
Re: VBA to search Column, find string and Highlight
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
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: VBA to search Column, find string and Highlight
Thanks all. I am just getting back this project. I will test on Friday!
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: VBA to search Column, find string and Highlight
That did the trick! and quite quick. processed 14K rows, with some cells being quite full..
Thanks!
Thanks!
-
- 2StarLounger
- Posts: 101
- Joined: 03 Feb 2018, 04:20
Re: VBA to search Column, find string and Highlight
after finding the string can it be replaced and highlighted the same,and how to add more color option instead of RED
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA to search Column, find string and Highlight
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?
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
Hans
-
- 2StarLounger
- Posts: 101
- Joined: 03 Feb 2018, 04:20
Re: VBA to search Column, find string and Highlight
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
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA to search Column, find string and Highlight
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
Hans
-
- 2StarLounger
- Posts: 101
- Joined: 03 Feb 2018, 04:20
Re: VBA to search Column, find string and Highlight
thanks a lot working fine
-
- 2StarLounger
- Posts: 101
- Joined: 03 Feb 2018, 04:20
Re: VBA to search Column, find string and Highlight
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
Run time error '1004'
Unable to set text property of the Character class
debug at line
rng.Characters(Start:=lngPos, Length:=lngFind).Text = strReplace
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA to search Column, find string and Highlight
Could you attach a sample workbook that demonstrates the error?
Best wishes,
Hans
Hans