Highlight Cell if Hyperlink is Lost?

[Amy]

I am having a huge problem losing hyperlinks and am
wondering if there is a way, other than conditional
formatting, to highlight a cell in which a hyperlink is
lost? I am unable to use conditional formatting because
the workbook is shared. Any ideas? I would be grateful
for any help. Thank you.

 

[Dave Peterson]

I see that you can't add hyperlinks to a shared workbook and you can't change
conditional formatting in a shared workbook.

But don't those go together kind of nice in your situation.

If you think so, too. Maybe you could use a User defined function that you
could use in your conditional formatting formula:


Option Explicit
Function HasHyperlink(rng As Range) As Boolean
Application.Volatile
On Error Resume Next
HasHyperlink = CBool(Len(rng(1).Hyperlinks(1).Address) > 0)
End Function

Put this is a general module in your workbook's project.

Then use:

=hashyperlink(x99)
(x99 is the address of the active cell)

and format the way you like.

This maybe one recalculation away from being accurate (removing or adding the
link didn't cause excel to recalculate). Hit F9 to force a recalc right before
you really want to trust it.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm