Search for string in a cell, Highlight if found

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

Search for string in a cell, Highlight if found

Post by bradjedis »

Greetings,

I have a situation where I have data in col A beginning at row 13 (total rows will vary). I need to search Col Z13, etc.., and if the data is found, Highlight RED BOLD.

row col A row col Z
13 ab123 13 r5tezx, ab123
14 df543 13 gz123, df543
...

Need to cycle thru each row/cell combo

Thanks,
Brad

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

Re: Search for string in a cell, Highlight if found

Post by HansV »

I'm sorry, that's not clear to me. What do you want to find?
Best wishes,
Hans

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

Re: Search for string in a cell, Highlight if found

Post by bradjedis »

If the data in cell A13 Matches any of the data in Z13, then highlight the data found in Z13 Red Bold. There can be additional data within the Z13 cell.

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

Re: Search for string in a cell, Highlight if found

Post by HansV »

Select Z13:Z100 or however far down the data go.
On the home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=ISNUMBER(SEARCH(A13,Z13))

Click Format...
Activate the Font tab.
Select Bold as font style and red as font color.
Click OK, then click OK again.

Remark: the above formula is not case-sensitive, so abc123 will match ABC123 and Abc123 too.
If you want the rule to be case-sensitive, use

=ISNUMBER(FIND(A13,Z13))
Best wishes,
Hans

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

Re: Search for string in a cell, Highlight if found

Post by bradjedis »

Sweet.

Did not Know I could do that type of searching in a conditional format.

Brad

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

Re: Search for string in a cell, Highlight if found

Post by bradjedis »

How about the Inverse. as in NOT a match?

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

Re: Search for string in a cell, Highlight if found

Post by HansV »

You'd use the same steps, but with the formula

=ISERROR(SEARCH(A13,Z13))
Best wishes,
Hans

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

Re: Search for string in a cell, Highlight if found

Post by bradjedis »

Ok we might need to do something more intense.

example:

Cell A25 contains 1A331 Cell Z25 contains 1A331, 23456 With this approach, the entire context are getting Highlighted. How can we limit the match to be Just the 1A331?

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

Re: Search for string in a cell, Highlight if found

Post by HansV »

Conditional formatting cannot highlight part of a cell. If you want to do that, you need to use VBA:

Code: Select all

Sub HighlightMatches()
    Dim r As Long
    Dim m As Long
    Dim a As String
    Dim p As Long
    Application.ScreenUpdating = False
    m = Range("Z" & Rows.Count).End(xlUp).Row
    For r = 13 To m
        a = Range("A" & r).Value
        p = InStr(Range("Z" & r).Value, a)
        If p > 0 Then
            With Range("Z" & r).Characters(Start:=p, Length:=Len(a)).Font
                .Color = vbRed
                .Bold = True
            End With
        End If
    Next r
    Application.ScreenUpdating = True
End Sub
Don't forget to remove the conditional formatting rule - it would override directly applied formatting.
Best wishes,
Hans

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

Re: Search for string in a cell, Highlight if found

Post by bradjedis »

Works Great!

Thank you,