Macro that looks for the name of active tab in the data on the sheet

[shahdelsol]

I am looking for macro that searches for the name of the tab in the data of the sheet and if found it turns it in bold and highlights it in red.
Let's say the name of sheet/tab is 0123456, I would like to look for 0123456 or 123456 in the sheet and if found , make it bold and red and if not found the popup would say no match found. The name of tab is usually a number that sometimes starts with zero(s), if that matters. However the zeros may or may not appear in the data itself. I appreciate that anyone can help!

 

[JoeMo]

Try this:

Sub TabNameInSheet()
Dim R As Range, fAdr As String
If Val(ActiveSheet.Name) = 0 Then
      With ActiveSheet.UsedRange
            Set R = .Find(ActiveSheet.Name, , xlValues, xlPart, , xlNext)
            If R Is Nothing Then
                  MsgBox "Sheet name not found on sheet"
            Else
                  fAdr = R.Address
                  Do
                        With R
                              .Font.Bold = True
                              .Interior.Color = vbRed
                        End With
                        Set R = .FindNext(R)
                  Loop While Not R Is Nothing And R.Address <> fAdr
            End If
      End With
Else
      With ActiveSheet.UsedRange
            Set R = .Find(Val(ActiveSheet.Name), , xlValues, xlPart, , xlNext)
            If R Is Nothing Then
                  MsgBox "Sheet name not found on sheet"
            Else
                  fAdr = R.Address
                  Do
                        With R
                              .Font.Bold = True
                              .Interior.Color = vbRed
                        End With
                        Set R = .FindNext(R)
                  Loop While Not R Is Nothing And R.Address <> fAdr
            End If
      End With
End If
End Sub

 

[shahdelsol]

Thank you so much. It did exactly what I expected. I just have a question. It seems like the code has been repeated twice. Is this on purpose or just a typo?

Try this:

Sub TabNameInSheet()
Dim R As Range, fAdr As String
If Val(ActiveSheet.Name) = 0 Then
      With ActiveSheet.UsedRange
            Set R = .Find(ActiveSheet.Name, , xlValues, xlPart, , xlNext)
            If R Is Nothing Then
                  MsgBox "Sheet name not found on sheet"
            Else
                  fAdr = R.Address
                  Do
                        With R
                              .Font.Bold = True
                              .Interior.Color = vbRed
                        End With
                        Set R = .FindNext(R)
                  Loop While Not R Is Nothing And R.Address <> fAdr
            End If
      End With
Else
      With ActiveSheet.UsedRange
            Set R = .Find(Val(ActiveSheet.Name), , xlValues, xlPart, , xlNext)
            If R Is Nothing Then
                  MsgBox "Sheet name not found on sheet"
            Else
                  fAdr = R.Address
                  Do
                        With R
                              .Font.Bold = True
                              .Interior.Color = vbRed
                        End With
                        Set R = .FindNext(R)
                  Loop While Not R Is Nothing And R.Address <> fAdr
            End If
      End With
End If
End Sub

 

[JoeMo]

Thank you so much. It did exactly what I expected. I just have a question. It seems like the code has been repeated twice. Is this on purpose or just a typo?

You are welcome.

Because your "numbers" may have leading zeroes (012345), I didn't know whether they are true numbers or just text entries. The first part of the code looks for text entries, while the second part (which is slightly different from the first) deals with numbers.

 

[shahdelsol]

Very nice! you are correct it could be either. Thank you very much!

 

[JoeMo]

Very nice! you are correct it could be either. Thank you very much!

You are welcome - thanks for the reply.