Ctrl-F Functionality from Cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bwilde@gmail.com - 01 Nov 2006 00:13 GMT

Hello,

I have an interesting (to me, at least) question with an easy work
around. Basically, I am working with some REALLY non-advanced users
who would like to search an excel spreadsheet for a text string. The
problem is that I'd like to have this search be done via a cell rather
than through the built-in Excel 'Find' feature. Is there a way to let
a user type some text into a cell, hit enter, and have Excel highlight
the row where the text is found? If this is possible, would it be
possible to highlight the row with the next instance of the text string
if the user hits enter again?

I realize that this is dumb, and that I should just teach them to use
Ctrl-F, but if there's a way to do this, we'd all be much happier.

Thanks.

Reply to this Message

Gary''s Student - 01 Nov 2006 01:44 GMT

This is just a sample.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

If Intersect(Target, Range("A1")) Is Nothing Then
Exit Sub
End If
Cells.Interior.ColorIndex = 0
v = Target.Value

For Each r In ActiveSheet.UsedRange
If r.Row <> 1 Then
If InStr(r.Value, v) Then
r.EntireRow.Interior.ColorIndex = 6
End If
End If
Next
End Sub

This goes in worksheet code. After data is entered in the worksheet, enter
a value in cell A1. The macro automatically hilights all rows containing the
contents of cell A1.

Signature

Gary's Student

> Hello,
>
[quoted text clipped - 12 lines]
>
> Thanks.

Reply to this Message

John13 - 01 Nov 2006 13:13 GMT

Gary's Student,

I too have interest in an in cell finding assist. I pasted the code
but got an error trying it out. Can you elaborate?

John13

> This is just a sample.
>
[quoted text clipped - 38 lines]
> >
> > Thanks.

Reply to this Message

Gary''s Student - 01 Nov 2006 14:28 GMT

I re-tested it:

1. It needs to be pasted in worksheet code rather than a module.

2. It will mark embedded stuff. If a cell contains hello world then typing
hello in cell A1 will trip the highlighting.

3. For the above reason, it will trip on 1234 is 3 is entered in cell A1

Signature

Gary's Student

> Gary's Student,
>
[quoted text clipped - 45 lines]
> > >
> > > Thanks.

Reply to this Message

Rate this thread: