highlight the word when excel finds text you are searching for

S

Sally M

When I use the "find" feature to search for a word on a large worksheet,
Excel 2000 brings me to the cell containing the word. Sometimes my cells
have a LOT of words in them. It would be very helpful, if Excel would also
highlight the word in the cell to make locating it easier. Does anyone know
how to do this?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
 
G

Gord Dibben

Sally

With a macro.........

Sub Highlight_Word()
Dim rng As Range
Dim cell As Range
Dim start_str As Integer
myword = InputBox("Enter the search string ")
Mylen = Len(myword)
Set rng = Selection
For Each cell In rng
start_str = InStr(cell.Value, myword)
If start_str Then
cell.Characters(start_str, Mylen).Font.ColorIndex = 3
End If
Next
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP



When I use the "find" feature to search for a word on a large worksheet,
Excel 2000 brings me to the cell containing the word. Sometimes my cells
have a LOT of words in them. It would be very helpful, if Excel would also
highlight the word in the cell to make locating it easier. Does anyone know
how to do this?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
Click to expand...

Gord Dibben MS Excel MVP
 
S

Sally M

Hi Gord,
Thanks for your fast reply.
I tried it; and it worked for the most part, but there are four concerns
that need to be tweaked:
1) I need the text search to not be case-sensitive. 2)The search only
occurs in one cell. I need it to look in the entire worksheet. 3) The color
of the font is changing, rather than the text being highlighted, and 4) I
need the highlighting to disappear and toggle back to normal when I'm
finished viewing each instance, so I can start a new search. Otherwise,
eventually my whole workbook will end up highlighted.
:))
 
D

Dave Peterson

#1. Use this line:
start_str = InStr(1, cell.Value, myword, vbTextCompare)
instead of:
start_str = InStr(cell.Value, myword)

#2. Select the range you want first--as many cells or as few cells as you want.

#3. You can't change the fill color under just those characters.

#4. Save before you run the code. Close without saving when you're done.

Or just format the font colors the way they used to be. Pretty easy if they all
were the same color to start.

Or do this against a copy of the workbook.
 
G

Gord Dibben

1. Add Option Compare Text above Sub Highlight_Word() or at top of module.

2. The code searches though whatever range you have selected

Change to Set rng = ActiveSheet.UsedRange

3. You cannot hightlight(color) only part of a cell. You must use the font
color change as your highlighing......or change text to Bold

4. Clear before the next search.

Add a line to clear the font color.

Revised version with a bit of error checking.................

Option Compare Text
Sub Highlight_Word()
Dim rng As Range
Dim cell As Range
Dim start_str As Integer
On Error GoTo endit
myword = InputBox("Enter the search string ")
If myword = "" Then Exit Sub
Mylen = Len(myword)
Set rng = ActiveSheet.UsedRange
rng.Cells.Font.ColorIndex = 0
For Each cell In rng
start_str = InStr(cell.Value, myword)
If start_str Then
cell.Characters(start_str, Mylen).Font.ColorIndex = 3
End If
Next cell
endit:
End Sub


Gord
 
S

Sally M

Thank you

Dave Peterson said:
#1. Use this line:
start_str = InStr(1, cell.Value, myword, vbTextCompare)
instead of:
start_str = InStr(cell.Value, myword)

#2. Select the range you want first--as many cells or as few cells as you want.

#3. You can't change the fill color under just those characters.

#4. Save before you run the code. Close without saving when you're done.

Or just format the font colors the way they used to be. Pretty easy if they all
were the same color to start.

Or do this against a copy of the workbook.
Click to expand...
 
S

Sally M

Thank you. I realize that what I'm trying to do is to make a search in Excel
behave like a search in Adobe Acrobat Professional. It's probably easier for
me to convert the Excel file to Adobe and do my search that way, so that I
can keep clicking "next" to see each instance. The worksheet that I am
working with already has different colored fonts representing different
categories, which is why I wanted to use a "highlight" function rather than
another change in font color. I didn't realize Excel wouldn't highlight.
Thanks again for your help. This VBA stuff is very interesting. . .
 
G

Gord Dibben

Thanks for the feedback.

Yes, the VBA stuff is interesting.

Also, the ability to give a background color(highlight) parts of a cell is a
common request but would be quite complex to achieve.

Gord

Thank you. I realize that what I'm trying to do is to make a search in Excel
behave like a search in Adobe Acrobat Professional. It's probably easier for
me to convert the Excel file to Adobe and do my search that way, so that I
can keep clicking "next" to see each instance. The worksheet that I am
working with already has different colored fonts representing different
categories, which is why I wanted to use a "highlight" function rather than
another change in font color. I didn't realize Excel wouldn't highlight.
Thanks again for your help. This VBA stuff is very interesting. . .
Click to expand...

Gord Dibben MS Excel MVP
 
Top