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

[Nelson78]

Hello everybody.

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

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]

How about

If Not foundVal Is Nothing Then foundVal.Interior.Color = vbYellow

 

[Nelson78]

How about

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]

Try

If Not foundval Is Nothing Then
    foundval.Interior.Color = vbYellow
    foundval.Font.Color = vbRed
End If

 

[Nelson78]

Try

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]

How about

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.

 

[Nelson78]

How about

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?

 

[Fluff]

As this is now a totally different question, you will need to start a new thread.
Thanks

 

[Nelson78]

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.

Regex: match and highlight string

Hello everybody. I've to match a string - cellz - in a range of cells. The task is ok like this: Dim cellz As Variant For Each cellz In sheet1.Range("A2:A" & lr_col) With CreateObject("VBScript.RegExp") .Global = True .IgnoreCase =...

www.mrexcel.com