Ctrl-F Functionality from Cell

[bwilde]

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.

 

[Guest]

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.

 

[John13]

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

 

[Guest]

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