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

Nelson78

Well-known Member
Joined
Sep 11, 2017
Messages
525
Office Version
  1. 2007
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?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
How about
Code:
If Not foundVal Is Nothing Then foundVal.Interior.Color = vbYellow
 
Upvote 0
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.
 
Upvote 0
Try
Code:
If Not foundval Is Nothing Then
    foundval.Interior.Color = vbYellow
    foundval.Font.Color = vbRed
End If
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
As this is now a totally different question, you will need to start a new thread.
Thanks
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top