highlight the word when excel finds text you are searching for
 

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

highlight the word when excel finds text you are searching for
 

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

highlight the word when excel finds text you are searching for
 

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

highlight the word when excel finds text you are searching for
 

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


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


--

Dave Peterson

highlight the word when excel finds text you are searching for
 

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

highlight the word when excel finds text you are searching for
 

Thank you

"Dave Peterson" wrote:

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


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


--

Dave Peterson

highlight the word when excel finds text you are searching for
 

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

"Gord Dibben" wrote:

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

highlight the word when excel finds text you are searching for
 

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

On Thu, 21 Sep 2006 06:25:02 -0700, Sally M
wrote:

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

"Gord Dibben" wrote:

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




Gord Dibben MS Excel MVP