search all in excel

[j2013]

hi, I am using the search in excel and there is a nice feature that that allows you to search all keyword in all cell which is great.
I wonder is there a way of marking all found cell in fill colour so that when i exit the search I can see them all easily.

thanks for all the answers.

 

[oldbrewer]

I am not familiar with that feature, but I am using excel 2000.

I suppose if you built the facility into a macro, every time it found a keyword it could colour the cell.

 

[VoG]

Some code that I wrote a long, long time ago

Sub FindHighlight()
Dim tempcell As Range, Found As Range, sTxt, FoundRange As Range, Response As Integer
Set Found = Range("A1")
sTxt = InputBox(prompt:="Enter value for search")
If sTxt = "" Then Exit Sub
Set tempcell = Cells.Find(What:=sTxt, After:=Found, LookIn:=xlValues, lookat:=xlWhole)
If tempcell Is Nothing Then
    MsgBox prompt:="Not found"
    Exit Sub
Else
    Set Found = tempcell
    Set FoundRange = Found
End If
Do
    Set tempcell = Cells.FindNext(After:=Found)
    If Found.Row >= tempcell.Row Then Exit Do
    Set Found = tempcell
    Set FoundRange = Application.Union(FoundRange, Found)
Loop
FoundRange.Interior.ColorIndex = 6
Response = MsgBox(prompt:="Clear highlighting", Buttons:=vbOKCancel + vbQuestion)
If Response = vbOK Then FoundRange.Interior.ColorIndex = xlNone
End Sub

 

[j2013]

hi all, thanks for the replies, I have played and found that in excel 2010 if you goto into find, type the word you are looking for and click "find all" you get a list of all found cells. now if you click the first line and goto to the end and click with Ctl and mouse click you choose all of them. now all you need to do is click on fill color and it works like a charm.

 

[István Hirsch]

After Find All just press Ctrl-A then Fill Color. You do not have to do anything with the list of found cells.