Macro for Searching and Selecting Keywords

I have a database of skills of the team members at my company. I am only using Excel because not everyone who will need to open it has programs such as Access. I would love to create a macro to search for keywords in the boxes and then highlight all the boxes with that word in it. Some of the boxes have multiple words separated by commas, so I would want it to look for words inside of the text as well. I know you can use "Find and Replace," but I am trying to make it easier on the more techinically challenged people that work here. I just want them to click on a button and type in a word they are looking for and then have it highlight the boxes with that word in it and make it visible.

I have 9 column headings, first one being the Team Member's Name; and then I have 36 rows of Team Members.

I really don't know much about macros so if you could break it down into simpler words, please do!

Thanks for your help!

Since the information you gave was limited i had to make a few assumptions:

Option Explicit

Sub Highlight()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
Dim lastrow As Long
Dim ivalue As Variant
Dim icell As Range
Dim Msg1 As String

'code
ivalue = Application.InputBox("Enter your search string here.", "Search & Highlight")
lastrow = ws1.Range("A" & Rows.Count).End(xlUp).Row

'err handling
If ivalue = False Then
    Exit Sub
ElseIf ivalue = "" Then
    GoTo Err0
End If

'code
For Each icell In ws1.Range("A1", "A" & lastrow)
    If InStr(1, icell, ivalue, vbTextCompare) Then
        icell.Interior.ColorIndex = 6
    End If
Next icell

Exit Sub
'err handling messages
Err0:
Msg1 = MsgBox("You did not enter a proper search value.", vbOKOnly, "Error")
End Sub

Assumptions:
1. Only one worksheet is involved and it is names Sheet1
2. Column A has information in the last most row.
3. The search criteria is taking place in column A.

If any of these things are incorrect you are going to need to adjust the code to meet your needs. More specifics are better.

Thanks so much for replying!

I do only have one worksheet labeled Sheet 1, but the information is really across the whole table as I have different people's information in the rows not in columns. I have the Team Member's Names going down Column A and then Columns B through I, there is information such as skills, personal interests, fluent languages, etc. If there is anything else you need to know to make a more accurate macro, let me know!

Thanks again

Ok. I have edited it took work on the entire range from A1 to Column I and the last used row. I have used the InStr property so that like you requested if there is a partial match it will highlight it yellow. Example: If a cell has a value of "thisisatest" and you type "test" in the input box, the cell will be highlighted. Enjoy.

Option Explicit

Sub Highlight()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
Dim lastrow As Long
Dim ivalue As Variant
Dim icell As Range
Dim Msg1 As String

'code
ivalue = Application.InputBox("Enter your search string here.", "Search & Highlight")
lastrow = ws1.Range("A" & Rows.Count).End(xlUp).Row

'err handling
If ivalue = False Then
    Exit Sub
ElseIf ivalue = "" Then
    GoTo Err0
End If

'code
For Each icell In ws1.Range("A1", "I" & lastrow)
    If InStr(1, icell, ivalue, vbTextCompare) Then
        icell.Interior.ColorIndex = 6
    End If
Next icell

Exit Sub
'err handling messages
Err0:
Msg1 = MsgBox("You did not enter a proper search value.", vbOKOnly, "Error")
End Sub

Sorry to keep bugging you..

When I try to run the macro a "Compile Error" comes up. It says that only comments may appear after End Sub, End Function, or End Property.

Thanks!

Hmmm. Sounds like maybe you have a double sub going on. Did you copy and paste the code within an already recorded macro. Submit the code that you put in the workbook for review.

as an option:

Sub test()
With Application: .ScreenUpdating = False
isearch = InputBox(Prompt:="Enter text or value to find", Title:="Search dialog")
With .ReplaceFormat.Font
    .ColorIndex = 3
    .Bold = True
End With
[a2:i37].Replace What:=isearch, Replacement:=isearch, Lookat:=xlPart, SearchFormat:=False, ReplaceFormat:=True
.ScreenUpdating = True: End With: End Sub

It works!!! Thank you so much!

I have one more question though. Is there any way to reset it without having to close it and not save it and then re-open it to start over? Would the best way to do that be with another macro?

Thanks!

I have one more question though. Is it possible to reset it somehow? Rather than just closing and not saving and then re-opening to start over. Would another macro be the best solution?

Thanks!