hyperlink to different sheet and find and highlight row VBA?

I know how to set up a hyperlink to a file or a sheet, and to a specific row, however i am trying to hyperlink a cell in sheet 1 to sheet 2 AND search the cells for specific content and highlight that row if found.
the information in sheet 2 changes and the orders are rearranged so i cant link a specific cell

if i click on cell A1 in sheet one i want it to find "smith" in sheet 2 and highlight that row.
is this possible?

Using vba is fine but i need a walkthrough.

Could use a few more details, but something like this?

1. Press Alt+F11 to bring up the VBA editor window
2. In the editor windowq, on the left hand side is the project explorer. Find your workbook.
3. Double click the Sheet1 object under that workbook
4. Paste the code below into the whitespace to the right
5. Code will run automatically any time you double click a cell on sheet1

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Value <> "" Then
Set c = Sheets("Sheet2").Cells.Find(Target.Value, lookat:=xlWhole)
If Not c Is Nothing Then c.EntireRow.Interior.Color = vbYellow
End If
End Sub

Originally Posted by walruseggman

Dunno if you want the Msgbox at the end, remove that line if not.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 3 Then
FiN = Range("B" & Target.Row).Value
LaN = Range("A" & Target.Row).Value
With Sheets("Sheet2").Range("A:A")
Set c = .Find(LaN, lookat:=xlWhole)
If Not c Is Nothing Then
    FirstAdd = c.Address
    Do
    If c.Offset(0, 1).Value = FiN Then
        Sheets("Sheet2").Activate
        Sheets("Sheet2").Rows(c.Row).Select
        Exit Sub
    End If
    Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> FirstAdd
End If
End With
MsgBox ("No matches on Sheet2")
End If
End Sub

So I out the code in and changed the sheet names to match what i have and verified that the names are correct. I double clicked on the first entry on sheet 1 (C3) which is the first entry on sheet 2 (row 2) AND GOT THE DREADED MESSAGE.... No matches found....

i checked the code and when i mouse over the "FiN = Range("B" & Target.Row).Value" is shows the name i am looking for and same with the "LaN = Range("A" & Target.Row).Value".....

So i am confused on why it is not finding it on sheet 2....
could it be that it is looking for those names in column c in sheet 2? because if so then that is our issue. Column A and B in sheet 1 correspond with A & B In sheet 2... Both sheets have last name in column A and and first name in column B.

Can you please post an example workbook that demonstrates the issue you are having? It doesn't have to be a ton of data, and it can all be made up, as long as the formatting is the same and the issue you're describing above is still present.

Sure thing. Here it is.
Thanks for all your help

Edit: Didn't see @skywriter's post before I posted. S/he correctly identified the problem.

The problems you were having are because the names on the first sheet are in uppercase, while the names on the second sheet are initial case. Not a big deal, just didn't know that needed to be accounted for. A few minor changes made to two lines of code. This should, in theory, now work regardless of what the case of any of the names are.

Anyway, tested as working on the example you posted.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 3 Then
FiN = Range("B" & Target.Row).Value
LaN = Range("A" & Target.Row).Value
With Sheets("Auto Order Sched").Range("A:A")
Set c = .Find(LaN, lookat:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then
    FirstAdd = c.Address
    Do
    If UCase(c.Offset(0, 1).Value) = UCase(FiN) Then
        Sheets("Auto Order Sched").Activate
        Sheets("Auto Order Sched").Rows(c.Row).Select
        Exit Sub
    End If
    Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> FirstAdd
End If
End With
MsgBox ("No matches on Auto Order Sched")
End If
End Sub

Good point. Not a whole lot to be done about misspellings other then to correct them.

oh and i also want to add a search bar at the top of sheet 1 that will search sheet 1 and select that row. kind of like what we just did.
so if I type in Marilyn Bridges (in anycase, or even if misspelled) it will look in columns A and B to see if there is a match and if so select that row.
I dont even know if its possible with as much as i am asking.

wow you folks are awesome..... Thank you so much. i didn't even think about the case... It works....

I am wanting to do some other things with this workbook, but i am trying to work through it. like if certain cells contain specific data then copy that row to the appropriate work sheet if it is not already there. (right now i have a button that copys all rows to the correct worksheets but does not remove the data already in that worksheet, and/or does not see if the information is already there.) If anyone wants to help PM me otherwise i'll post in the forums.

can anyone give me some ideas?

General policy at this Forum is to post new threads for each new problem you need help with. This isn't without reason; someone else may have a problem similar to what your asking, but looking at the title of this thread it would not be obvious this thread addresses those problems.

Start new threads and I'm sure someone will be able to help you. And example workbooks are always helpful!

Also: Please mark this thread as SOLVED (it has solved the original problem), and any reputation points (the * in the lower left of each post) would be appreciated.

you are absolutly right. I have already made a new thread for the next issue of a search box.
http://www.excelforum.com/excel-prog...ml#post4138515