A few weeks ago I got a question about hyperlinks. A list of soccer clubs was kept in Excel (4000+). Some of them had a hyperlink some didn't.
How to see this quickly? A possible solution is to show them in different colors:
Private Sub ColorHyperlinks()
' color cells with hyperlinks
Dim rngCel As Range
Application.ScreenUpdating = False
For Each rngCel In Selection
If rngCel.Hyperlinks.Count <> 0 Then
' cell has a hyperlink make yellow (6)
rngCel.Interior.ColorIndex = 6
Else
' remove color: -4142
rngCel.Interior.ColorIndex = -4142
End If
Next rngCel
Application.ScreenUpdating = True
End Sub
Or you can make the cells with hyperlinks bold:
Private Sub BoldHyperlinks()
' makes cells with hyperlink bold
Dim rngCel As Range
Application.ScreenUpdating = False
For Each rngCel In Selection
If rngCel.Hyperlinks.Count <> 0 Then
' make bold:
rngCel.Font.Bold = True
Else
' Not Bold
rngCel.Font.Bold = False
End If
Next rngCel
Application.ScreenUpdating = True
End Sub
In the next version of ASAP Utilities I'll build in a function to extract the hyperlinks:
Private Sub ExtractHyperlinks()
' extracts hyperlink and put it in the next column
Dim rngCel As Range
Application.ScreenUpdating = False
For Each rngCel In Selection
If rngCel.Hyperlinks.Count <> 0 Then
' cell has a hyperlink:
rngCel.Offset(0, 1).Value = rngCel.Hyperlinks(1).Address
End If
Next rngCel
Application.ScreenUpdating = True
End Sub