Hyperlink Style Search Macro

[miketurn]

I am looking for a macro (I believe it will be two macros needed) that could do the following....

Say you have a spreadsheet with a whole bunch of BOLD header titles throughout the page.
At the top of the spreadsheet is a list of all these BOLD header titles.
I would like to be able to click on an item in this list and have it run a search with no dialog boxes and find the first match it can and navigate to it.

(Cannot use a hyperlink in this situation because as rows are added and deleted, the cell locations of the headers will change)


EXAMPLE
Note: The numbering of the cells in this example are just an example, would like these macros to be dynamic (used for any columns / rows)

1 [U][B]GLOSSARY[/B][/U] (HEADER) (LIST AT TOP OF SPREADSHEET)
2 CARS
3 FRUITS
4 FURNITURE
....

10 [U][B]CARS[/B][/U] (HEADER)
11 camry
12 accord
13 mustang
14 corolla
15
16
.....
200 [U][B]FRUITS[/B][/U] (HEADER)
201 apple
202 banana
203 orange
204 grape
.....
305 [U][B]FURNITURE[/B][/U] (HEADER)
306 chair
307 table
308 dresser
309 bed

I would like to be able for example to....
1.) Select with my mouse cell number 3 = "FRUITS"

2.) By clicking on this cell, it activates a macro (much like a hyperlink) that does a search of its content, which in this case is "FRUITS".
No search dialog boxes, just find the first match.
Also in this search action, only return search match for this text value if it is BOLD.

3.) The macro will then navigate to the first BOLD "FRUITS" text it finds.
The built in Search with Excel does this navigation automatically when it finds a match, if possible would be great if when it navigates to a header (a search match) that it navigates / adjusts your screen so that the search match is at the top of your screen.


So as mentioned, I would imagine this would require two macros.
1.) A macro that allows you to run another macro by clicking on a specific cell.
Similar to this one below, but somehow more dynamic, because if you have to add new header titles in the GLOSSARY list, the cell location values will change.
https://www.mrexcel.com/forum/excel-questions/7971-how-do-i-run-macro-point-cell-selection.html

2.) A macro that performs the search and navigates to the match (as described above)


Thank You to anyone that takes the time to read this and is willing to try to solve it.

 

[Eric W]

Are you sure that a hyperlink won't work? Try this in A2:

=HYPERLINK("#"&CELL("address",Sheet1!A10),"CARS")

Now you can add/delete rows, or even drag the A10 cell around, and the A2 hyperlink will still point to the new location of A10.

If this doesn't work, we could write a SelectionChange macro that searches the sheet as you imagine, but this would be more efficient.

 

[Fluff]

You need to Double click on the cell you want for this to work.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


    Application.FindFormat.Font.FontStyle = "Bold"
    Cells.Find(What:=Target.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=True).Activate
    ActiveWindow.ScrollRow = ActiveCell.Row


End Sub

It needs to go in the sheet module, rather than a normal module

 

[miketurn]

@Eric W
Thank you for your response here as well
No, I did not try this method of adding a hyperlink, to be honest I don't use formulas and functions a ton, so I did not know of this method.
I unfortunatley ran into some trouble when trying to apply this.
I am using version 2003 and as mentioned I also don't know a ton about running functions and formulas so it could be something on my end.
How you describe it however, sounds almost exactly what I am looking for.

When I type in the command you mentioned, it ends up opening a window called "Update Values Sheets" and it is looking for me to link a file to something? Is this what it should do? The only file I could think of linking was the same excel spreadsheet it is located in, but that does not work.

Again what you mention sounds perfect though, that it will automatically update on its own as its row number changes, and the fact that I would have to link a whole bunch of macros sounds nice to

If you have any ideas of what I might be doing wrong, please let me know.
Also thank you again for all the help you have been providing me.

 

[miketurn]

@Fluff
Thank you for your response.
This approach is also a pretty cool one, the only downside is the having to double click to activate it.
This leads to not being able to edit any of the cells by double clicking, you will have to use F2 to enter them.
If you try to edit by double clicking you get the following error

Run-time error '91': 
Object variable or With block variable not set

I love that it only requires a single macro and I especially love how it navigates the search to the top of the screen like I requested, that part really works well.
As mentioned the only problem is that doubleclicking which is needed to run the macro.
By force of habit I would be double clicking to edit and each time I do so it would be calling up that debug error message

Thank You again for your response.

 

[Fluff]

This should deal with the errors

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim Rng As Range
    
    Application.FindFormat.Font.FontStyle = "Bold"
    Set Rng = Cells.Find(What:=Target.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=True)
        
    If Not Rng Is Nothing Then
        Rng.Select
        ActiveWindow.ScrollRow = Rng.Row
    End If


End Sub

 

[Eric W]

Well, sorry about that. HYPERLINK was introduced in Excel 2007, so you're out of luck there. You'll need to use a macro, like Fluff is trying. Good luck.

 

[Fluff]

Well, sorry about that. HYPERLINK was introduced in Excel 2007, so you're out of luck there.

Hyperlink is available in 2003

 

[Eric W]

I don't have a version of 2003 to check, but I'm going off of this page:

https://support.office.com/en-us/article/HYPERLINK-function-333c7ce6-c5ae-4164-9c47-7de9b76f577f

 

[Fluff]

That's interesting, but they definitely do exist in 2003, as I've used them.