Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Highlight cell background when using Find & Select



I have a basic Excel spreadsheet for my checking account. At times I need to search for a check #, payee, etc. I can easily do this with "Find & Select."

What I would like to do is highlight the cell background of the search results to make it easier to find them. I do not want the cell background to change permanently. For example, the cell background should revert to "No Fill" after hitting the Esc or F9 key.

A relatively simple solution without using a macro or VBA would be ideal.

Thanks for any suggestions.





I got an Alert that a (this) "Post on which you commented has been updated" but I can't see what has changed.

Did you mean to add detail or an Excel file to your original question? Ir did you already try Willie's solution and mine? (You have to be logged in to see files)
John_Ru (rep: 4937) Feb 21, '23 at 1:36 pm
Add to Discussion


Selected Answer

Hello Jim and welcome,

A "relatively simple solution" does use VBA - a Worksheet_SelectionChange macro. The following macro is placed in the worksheet code window (right click the sheet tab and select "View Code"). Then copy and paste this code in the window that opens. (The workbook will need to be saved as an ".xlsm" format now that there is a macro in it)

The following code runs every time the cell selection changes - either by you clicking on a cell or if "Find / Replace" selects a cell.

First, it will capture the existing cell-fill colour number, then change the cell-fill to Cyan Blue, wait 3 seconds, and then change the cell-fill colour back to its original colour (if any)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 ' macro written Feb. 20/23 by WillieD24 for TeachExcel

If Target.Count > 1 Then Exit Sub    ' if more than one cell is selected the macro quits

Dim OrigCol As Integer

OrigCol = Target.Interior.ColorIndex     ' original colour index number of current cell-fill

Target.Interior.ColorIndex = 8     ' cyan blue (change to suit)

Application.Wait (Now + TimeValue("00:00:03"))     ' 3 second pause (change to suit)

Target.Interior.ColorIndex = OrigCol     ' return cell-fill colour to its original colour

End Sub

You can see how this works in the attached file.

If this meets your needs, be sure to mark the answer as selected.

Cheers   :-)



@Willie That works and I agree- I can't see a non-VBA solution for Jim's requirement.

@Jim. Willie's solution avoids using XML to intercept or modify Ribbon actions but, for me, the three second delay on any selection change would be intrusive so I'd reduce it to one second (perhaps opting for a vivid red temporary fill too).
John_Ru (rep: 4937) Feb 20, '23 at 5:43 pm
@Willie Well done getting your Answer for Jim Selected- it's great to see you get on the Leaderboard. Keep up the goodwork! 
John_Ru (rep: 4937) Feb 24, '23 at 9:53 am
@John   Thanks. It's always a good feeling when I am able to help someone solve their challenge. (unless you beat me to the punch  LOL).
WillieD24 (rep: 210) Feb 24, '23 at 12:40 pm
Agreed/ likewise! 
John_Ru (rep: 4937) Feb 24, '23 at 5:33 pm
Add to Discussion


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
                '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.


Answer the Question

You must create an account to use the forum. Create an Account or Login