Highlight Find

[Orangeanorak]

In excel 2003 when using the find function to search for a particular name or number is it possible to have that name or number highlighted in a different colour to allow me to see it quickly

Thank you

 

[kevatarvind]

try below code

Sub Find_Highlight()
On Error GoTo err_desc
Dim found As String
found = InputBox("What To Find", "Find And Highlight")
If found <> "" Then Cells.Find(what:=found, Lookat:=xlWhole).Select
Selection.Interior.ColorIndex = 6
Exit Sub
err_desc:
If Err.Number = 91 Then MsgBox "Value Not found."
End Sub

 

[Orangeanorak]

Thank you - I will try that

 

[Orangeanorak]

Grateful for furter advice please
How can the code be changed to highlight all the occurrences of the name or number i.e. find all

 

[Orangeanorak]

ADVERTISEMENT

And id like to highlight in colour the actual word or number I am trying to find - not highlight the cell background please

 

[farmerscott]

try changing......

Selection.Interior.ColorIndex = 6

to

Selection.Font.Color = vbYellow

FarmerScott

 

[Orangeanorak]

Thank you however this does not show all the occurrences of the word or number I want to highlight. Also most of my cells contain more than one word. This formula finds only cells with one word in it.

As an example I want to find and highlight in a different colour ALL occurences of the word Smith

cell a1 John Smith
cell a2 Mary White
cell a3 James Smith
cell a4 Smith Street
cell a5 Jack Smart
cell a6 Blacksmith
cell a7 Sam has a friend called Fern Smith