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



On Tue, 19 Sep 2006 07:38:02 -0700, Sally M
wrote:

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.
:-))

"Gord Dibben" wrote:

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 InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

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


Gord Dibben MS Excel MVP



On Mon, 18 Sep 2006 13:15:02 -0700, Sally M <Sally
wrote:

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/comm...et.f unctions

Gord Dibben MS Excel MVP