Input box to find and highlight entered word(s)

[Melynnie]

I have a sheet with out 4000+ products on it (item numbers & descriptions) - I would like to create a search option for the guys in the field (much like in MSWord) where you enter keyword(s) and it will highlight every instance of the word. (highlight word, cell, entire row I dont care.)

I realize you can use the generic "Find" however, many of the guys do not do much other than enter information & print what they need from Excel. The easier I can make it for them, the happier they will be.

this is what i have (please dont laugh) I could be WAY off base

Dim sUserString As String
sUserString = InputBox("Enter Keyword", _
"Keyword")

Do
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.Highlight = True

With Selection.Find
.Text = sUserString
.Replacement.Text = sUserString
.Forward = True
.Wrap = wdFindstop
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
If Not Selection.Find.Found Then Exit Do
MsgBox "egads!!"



Loop

 

[RonB1111]

Easiest way is to turn on AutoFilters in the worksheet. Do this by 1st selecting the header row and then clicking the "Filter" icon under the "Data" tab. Little icons with a triangle will appear in each header row cell. Instruct the guys to click the triangle in either the part# or description header cell, and then in the drop down box to select "custom" and use either "equals" or "contains" the part# or desc they're looking for.

 

[Melynnie]

This too has been considered (trust me, it would make MY life easier).

Here is what I am dealing with (I apologize for being vague in my origional post)- these guys are in the field and most are "old school", always in a hurry and very impatient (In other words, they don't want to learn something new - it's just one more thing to remember). I am trying to clear up a bottle neck (when they request samples) because using their current method is "too much work" therefore they do not use the process put in place.

If I am able to make it "babysitter like" (so to speak), I think i will get what I need accomplished in house as well as on the field.

It's like getting your kids to do their chores - make it fun and easy and you won't get any "grief"

Any help and more suggestions are greatly appreciated!

Melynnie

 

[RonB1111]

Another option is to apply conditional formating to the entire worksheet. Pick a cell (say B1) where the guys will type in either the entire or partial part# or description. To apply conditional formating to the entire worksheet, select all cells in the worksheet and use this formula: =FIND($B$1,A1) --- then click the format button and select any pattern color

When something is typed in cell B1, any cell that has that string within it will be highlighted.

You still may want to turn autofilters on if you have Excel 2007 or 2010. Some of the old school guys may eventually discover it's easier to filter by color rather than scrolling thru 4000 rows looking for colored cells.