Selected Answer
Jim
I've taken a slightly different approach to Willie but still using VBA. This requires you to use VBA to search (rather than Find within the Find and Select ribbon item).
In the attached file, you'll see some text data in A1:F9 (but this could be expanded, provided you insert columns so that column I is moved at least one column away from the data range).
Enter some text (or value) in yellow cell J1 then click the blue "button" to the left of it. Any cells containing that value should then be filled cyan (as Wille did) and the first instance of the text within that cell will be bold red. Press the Esc key and it will be reset.
The Esc key does this because I've set an event macro when the file opens:
Private Sub Workbook_Open()
' assign a macro to the ESC button in this file
Application.OnKey "{ESC}", "Esc_Clear"
End Sub
which mean when Esc is pressed, it invokes this macro (in Module1):
Sub Esc_Clear()
On Error Resume Next
' using range set in Findmatches...
With OutRng
'retore to plain text, white background
.Interior.ColorIndex = 0
.Font.Color = vbBlack
.Font.Bold = False
End With
End Sub
(which acts on a range defined by a search using the blue button)
The Esc key is returned to normal by this event macro (which ast before the file is closed):
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' clear macro assignment to the ESC button
Application.OnKey "{ESC}"
End Sub
.
The code that does all the works finding J1 is this (with some comments to help you), where you could change the cells in bold near the start:
Dim OutRng As Range
Sub FindMatches()
Dim Cll As Range, Srch As String, nFirst As Long, firstAddress As String
'state where the data starts (but ignore first row) and where the search value is
Srch = Range("J1").Value
Set OutRng = Range("A1").CurrentRegion.Offset(1, 0)
' first clear the range of any previous search
Call Esc_Clear
' search for the value in the range
With OutRng
Set Cll = .Find(Srch, LookIn:=xlValues, SearchOrder:=xlByRows)
If Not Cll Is Nothing Then
firstAddress = Cll.Address
' find instance of value
Do
'highlight first instance of text in red
nFirst = InStr(1, Cll.Text, Srch, vbTextCompare)
With Cll.Characters(Start:=nFirst, Length:=Len(Srch))
.Font.ColorIndex = 3
.Font.Bold = True
End With
' fill the cell as cyan
Cll.Interior.ColorIndex = 8
'try to find again
Set Cll = .FindNext(Cll)
Loop While Not Cll.Address = firstAddress
End If
End With
End Sub
Note that OutRng is defined before the code so that it also available to the Esc_Clear macro and that you need to have done a search before Esc will clear any formatting.
I've set J1 as Text (so in my example, you could seacrh for 005 to get check numbers or 5 to include values).
If you have say six of seven columns of data (including a heasder row) and many rows, you could paste them over my test data and it should work the same.
Hope this works for you. If so, please remember to mark this Answer as Selected.
