Vba: search a string in a range, if found highlights the cell

Nelson78

Active Member
Joined
Sep 11, 2017
Messages
402
Hello everybody.

I'm dealing with searching a string in a range:

Code:
X = "ABCDEF"

Dim foundVal As Range
    Set foundVal = Worksheets(2).Range("C2:C21").Find((X), LookIn:=xlValues, lookat:=xlPart)
My deadlock is: I need to highlight, for example in yellow RGB(0, 255, 0), the cell where the string is found.

How can I perform the task?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,205
Office Version
365
Platform
Windows
How about
Code:
If Not foundVal Is Nothing Then foundVal.Interior.Color = vbYellow
 

Nelson78

Active Member
Joined
Sep 11, 2017
Messages
402
How about
Code:
If Not foundVal Is Nothing Then foundVal.Interior.Color = vbYellow
The first test is positive.

What about painting of another colour the string found in foundVal?
I mean: paint the cell yellow and red the string inside it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,205
Office Version
365
Platform
Windows
Try
Code:
If Not foundval Is Nothing Then
    foundval.Interior.Color = vbYellow
    foundval.Font.Color = vbRed
End If
 

Nelson78

Active Member
Joined
Sep 11, 2017
Messages
402
Try
Code:
If Not foundval Is Nothing Then
    foundval.Interior.Color = vbYellow
    foundval.Font.Color = vbRed
End If
I mean only the part of the string that has been matched.

For example:
searching the string ABCDEF in GFDGDAGDAABCDEFGADGDGADGA

I need:

GFDGDAGDAABCDEFGADGDGADGA
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,205
Office Version
365
Platform
Windows
How about
Code:
If Not FoundVal Is Nothing Then
    FoundVal.Interior.Color = vbYellow
    FoundVal.Characters(InStr(FoundVal.Value, x), Len(x)).Font.Color = vbRed
End If
But this will not work if the cell contains a formula.
 
Last edited:

Nelson78

Active Member
Joined
Sep 11, 2017
Messages
402
How about
Code:
If Not FoundVal Is Nothing Then
    FoundVal.Interior.Color = vbYellow
    FoundVal.Characters(InStr(FoundVal.Value, x), Len(x)).Font.Color = vbRed
End If
But this will not work if the cell contains a formula.
Unfortunately the task is becoming more complex day by day.

The bigger complications are:
1) I need to find all the occourences, not just the first;
2) I need to find the exact word: for instance, if I'm searching "MARK" in "MY NAME IS MARK" is ok, but if I'm searching "MARK" in "I STUDY MARKETING" is not ok;
3) connected to the point 2, the string I'm searching for could be composed by more then one world and I've to search them in the correct order: for instance "MARK TWAIN". As a consequence, if I'm searching in "MISTER MARK TWAIN IS FROM FLORIDA" is ok, but if I'm searching in "MARK IS HIS NAME, TWAIN IS HIS SURNAME" is not ok.

By the way: could a regex solution be a better one?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,205
Office Version
365
Platform
Windows
As this is now a totally different question, you will need to start a new thread.
Thanks
 

Nelson78

Active Member
Joined
Sep 11, 2017
Messages
402
As this is now a totally different question, you will need to start a new thread.
Thanks
Thank you.

I've gone a little bit ahead. I think of being near the solution.


 
Last edited:

Forum statistics

Threads
1,077,777
Messages
5,336,192
Members
399,069
Latest member
haxahid

Some videos you may like

This Week's Hot Topics

Top