is hyperlink valid ?

[Jakobshavn Isbrae]

I have a spreadsheet in which column H contains hundreds of hyperlinks. I
have a problem with links becoming "stale"

Is there a wasy, using VBA, to scan down the column and high-light any cells
containing a "stale" link ?

Thanks in advance for any help.
--
jake

[NickHK]

As hyperlinks can be refer to numerous types of destinations, (ranges,
files, specific locations within files, etc) the only sure way would be to
account for each of the different types. Or just .FollowHyperlink and check
for errors. That would mean opening each of the applications responsible for
the file in the hyperlink, which be quite time consuming.
If you just want to test for valid external files, then maybe this should
get you started:

Private Sub CommandButton1_Click()
CheckHyperlinks Cells
End Sub

Public Function CheckHyperlinks(argRange As Range) As Long
Dim HLink As Hyperlink

For Each HLink In argRange.Hyperlinks
If IsFileValid(HLink.Address) = False Then HLink.Range.Interior.Color =
vbRed
Next

End Function

Public Function IsFileValid(FilePathName As String) As Boolean
On Error GoTo Handler

GetAttr (FilePathName)
IsFileValid = True
Exit Function
Handler:
IsFileValid = False
End Function

Not how mapped drives behave here and remote files (e.g. on e the web) would
need another approach.

NickHK

"Jakobshavn Isbrae" <Jakobsha...@discussions.microsoft.com> wrote in
message news:8369BBE5-CD62-48D1...@microsoft.com...

[Jakobshavn Isbrae]

Thank you very much. I have been able to adapt you code and it has already
saved me much time.

Thanks again
--
jake