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
Search for string in a cell, Highlight if found
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Search for string in a cell, Highlight if found
I'm sorry, that's not clear to me. What do you want to find?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Search for string in a cell, Highlight if found
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.
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Search for string in a cell, Highlight if found
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))
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
Hans
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Search for string in a cell, Highlight if found
Sweet.
Did not Know I could do that type of searching in a conditional format.
Brad
Did not Know I could do that type of searching in a conditional format.
Brad
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Search for string in a cell, Highlight if found
How about the Inverse. as in NOT a match?
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Search for string in a cell, Highlight if found
You'd use the same steps, but with the formula
=ISERROR(SEARCH(A13,Z13))
=ISERROR(SEARCH(A13,Z13))
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Search for string in a cell, Highlight if found
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?
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?
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Search for string in a cell, Highlight if found
Conditional formatting cannot highlight part of a cell. If you want to do that, you need to use VBA:
Don't forget to remove the conditional formatting rule - it would override directly applied formatting.
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
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Search for string in a cell, Highlight if found
Works Great!
Thank you,
Thank you,