Get cell of broken Hyperlink

  • Thread starter Thread starter BVHis
  • Start date Start date
B

BVHis

I've got a workbook with multiple hyperlinks in it. In this workbook i
a macro to verify the existance of each link.

What I'd like to be able to do is select the cell of the broken lin
(if and when one is found) and change the color of the cell.

Here's what I've got for verifying the existance of the links (sorry,
don't remeber who I "borrowed" this from in this forum).

Public Sub VerifyLinks()
Dim sAddr As String
Dim lnk As Hyperlink

For Each lnk In ActiveSheet.Hyperlinks
sAddr = lnk.Address
Debug.Print lnk.Address
If Dir(sAddr) = "" Then
MsgBox "Link NOT found!"
' Select cell and change color
Else
MsgBox "Link found!"
End If
Next
End Sub

Thanks in advance!

Matt
 
if that successfully finds the broken link, then to color the cell:

Public Sub VerifyLinks()
Dim sAddr As String
Dim lnk As Hyperlink

For Each lnk In ActiveSheet.Hyperlinks
sAddr = lnk.Address
If Dir(sAddr) = "" Then
'MsgBox "Link NOT found!"
' Select cell and change color
lnk.parent.Interior.ColorIndex = 6
Else
'MsgBox "Link found!"
End If
Next
End Sub
 
Back
Top