Navigate To The Next Cell With Bold Text Macro

[miketurn]

I am looking for two very similar tools.

I believe I would only need this to work in Column A.
The first tool I am looking for is a macro that when activated will navigate the selection cursor to the next cell below that contains bold text. If you then activate this tool again, it will do the same, navigate the cursor to the next cell that contains bold text.

The other tool I am looking for is the same as above, but instead of finding the next cell with bold text below, it finds the next cell containing bold text above where the cursor is currently placed and navigates to it.

So basically as you can see the two basically allow the OP the ability to navigate up or down to the next bold text item.

If possible I would like when the cursor navigates to the next bold text cell / row that it ends up at the top of the screen.
I think the nature of how Excel was programmed to navigate does not allow for this, but I am not sure.
I am going to make a specific post about this and link it below.

https://www.mrexcel.com/forum/excel...top-your-screen-within-macro.html#post4928171


If anyone has any macro like this, please share.
Thank You to anyone who reads this

 

[Momentman]

You could use something like this

Sub FindPrevious()
    On Error Resume Next
    Dim RngToSearch As Range
    Dim FoundEntry As Range
    Set RngToSearch = Range("A:A")
    Application.FindFormat.Font.FontStyle = "Bold"
    FoundEntry = RngToSearch.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
        , SearchFormat:=True).Activate
    'FoundEntry = RngToSearch.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=True).Activate
    Application.Goto FoundEntry, Scroll:=True
End Sub

The code in bold is if you want to search forward while the one currently in use is if you want to search backwards

See if they work for you

 

[Peter_SSs]

Momentman/miketurn

A few comments:

1. If the user had previously searched for yellow formatted cells, the suggested code will be looking for cells that are both yellow and bold, so I would suggest clearing the 'Find formatting' first. For a similar reason I think it better to clear that formatting at the end of the code as it might catch the user out with their next Find.

2. I may be wrong, but I suspect the user doesn't want to navigate to cells that may be formatted as bold but not contain anything so my suggestion only looks for bold cells with some content.

3. I would recommend against having code that is completely covered by On Error Resume Next as it could mask errors that you really need to know about.

Again, change xlNext to xlPrevious to search up

Sub Find_Down()
  Dim Found As Range
  
  Application.FindFormat.Clear
  Application.FindFormat.Font.FontStyle = "Bold"
  Set Found = Columns("A").Find(What:="?*", After:=Range("A" & ActiveCell.Row), SearchDirection:=xlNext, SearchFormat:=True)
  If Found Is Nothing Then
    MsgBox "No bold cells found"
  Else
    Application.Goto Reference:=Found
  End If
  Application.FindFormat.Clear
End Sub

 

[Momentman]

Momentman/miketurn

A few comments:

1. If the user had previously searched for yellow formatted cells, the suggested code will be looking for cells that are both yellow and bold, so I would suggest clearing the 'Find formatting' first. For a similar reason I think it better to clear that formatting at the end of the code as it might catch the user out with their next Find.

2. I may be wrong, but I suspect the user doesn't want to navigate to cells that may be formatted as bold but not contain anything so my suggestion only looks for bold cells with some content.

3. I would recommend against having code that is completely covered by On Error Resume Next as it could mask errors that you really need to know about.

Again, change xlNext to xlPrevious to search up

Sub Find_Down()
  Dim Found As Range
  
  Application.FindFormat.Clear
  Application.FindFormat.Font.FontStyle = "Bold"
  Set Found = Columns("A").Find(What:="?*", After:=Range("A" & ActiveCell.Row), SearchDirection:=xlNext, SearchFormat:=True)
  If Found Is Nothing Then
    MsgBox "No bold cells found"
  Else
    Application.Goto Reference:=Found
  End If
  Application.FindFormat.Clear
End Sub

Wonderful contributions, as always

 

[miketurn]

@Momentman / @Peter_SSs
Thank you guys for your responses.
Peter I tried your updated version and overall it accomplishes what I was looking for.
I did run into a couple of things that I was wondering if you guys may offer some insight on.

1.) I mentioned that I only need this for Column A, the problem is I made an assumption that turned out to not be true, some of my spreadsheets use Column B (and sometimes C), however, the main bold items I need to navigate up and down to are in Column A, but with some of my bold headers I merge Column A and B (and C if it exists) together. My assumption was that because these "merged" column rows begin in Column A, that these macros would be able include them in their bold text finds, unfortunately they skip any merged column rows with bold text completely because they are not considered to be in Column A.
Is there any way to be able to somehow include these merged column rows that have bold text in the macros provided?
(Not a big deal, I will mainly use these macros in spreadsheets that only use Column A, this update will just allow these macros to be used more universely)

2.) When the "find the next bold text" macro navigates to the next bold text item, if the bold text is off the screen it will navigate to it showing it at the very bottom of my screen, is there a way to have it navgate to this bold text item at the top of the screen.
I asked about this here...
https://www.mrexcel.com/forum/excel...top-your-screen-within-macro.html#post4928171
so far it appears that it can be done, but the examples given on this thread are based on having to give a specific cell column letter and row number in order to achieve this.
Can this be applied to the result of the bold text find?

3.) If I am at the top of my spreadsheet and I use the "xlPrevious" tool it will scroll to the bottom of the spreadsheet to find the previous bold item.
If I am at the last bold item, when I use the "xlNext" version it will scroll to the top of the spreadsheet to find the next bold item.
Is there a setting that can be added to both of these tools that will make them stop when they get to the first or last bold text item, so they don't cycle back around to the beginning or end?

Again if any of these requests are too complex do not worry about it, just the fact that you guys have gotten me this far is amazing, the tools really work great as they are.
Thank you guys again for the help.

 

[Peter_SSs]

In relation to point ..

1. Merged cells are often a problem to deal with in code. Would this work for you? Where you have cells merged across columns A:C (or A:B)
- unmerge them
- Select the 3 cells (or 2) and use the cells Formatting dialog to set 'Center Across Selection' in the Horizontal section of the Alignment tab.
The appearance should be the same and the code should find the cells.

2. and 3. Add the blue text to my existing code

If Found.Row > ActiveCell.Row Then Application.Goto Reference:=Found, Scroll:=True

Of course, for the corresponding 'FindUp' code, change the > to <

 

[miketurn]

@Peter_SSs
Amazing! You are correct, that one line of text solved both 2 and 3, and I was also able to update a couple other macros I have with it.

As far as issue number 1 with the "merged" cells, you and others taught me about the issues that "merged" cells can cause, which is why I stopped using them 99%, the only time I may use them is for a header because it allows me to alter the format (background fill color / border around it) a little easier. The text is usually left justified, but that "merge across" recommendation is a really cool tip that I will use in the future as well.
This tool works so great, this one is definitely one of my favorites.
Thank You so much for your continued help, I truly appreciate it


Just in case anybody is interested in this macro in the future, here is the version with this new code added, which makes the macro navigate the bold text to the top of the screen, and stops it from looping to the beginning. It works, so I believe I put it in the right location.

Sub Find_Down()
  Dim Found As Range
  
  Application.FindFormat.Clear
  Application.FindFormat.Font.FontStyle = "Bold"
  Set Found = Columns("A").Find(What:="?*", After:=Range("A" & ActiveCell.Row), SearchDirection:=xlNext, SearchFormat:=True)
  If Found Is Nothing Then
    MsgBox "No bold cells found"
  Else  [COLOR=#0000CD][B]
If Found.Row > ActiveCell.Row Then[/B][/COLOR] Application.Goto Reference:=Found[COLOR=#0000CD][B], Scroll:=True[/B][/COLOR]   
End If
  Application.FindFormat.Clear
End Sub