use conditional format to highlight links in spreadsheet

Microsoft Office excel 2007
October 4, 2012 at 16:28:19
Specs: Windows XP
Apologies if this is a duplicate post.
I need to format several worksheets to display cells with links in a specific color. I have used a rangename get.cell(448,indirect("rc",false)) to apply conditional formatting to highlight cells with formulas, but cannot figure out how to highlight cells with links. Is there a way to do this?
thank you. jennifer

See More: use conditional format to highlight links in spreadsheet

October 4, 2012 at 17:57:37
I assume you mean that you used the following, because I don't think that 448 is a valid argument for get.cell. I couldn't get it to work until I changed it to 48.


As far as I know there is no get.cell argument for hyperlinks. Therefore I don't think you can use the Named Range/Conditional Formatting method that you used above.

The following code is a modified version of something I found at:

The original code was written as a means to select all non-formula based Hyperlinks in a sheet. I modified it to color all non-formula based Hyperlinks in a sheet. A ColorIndex of 6 fills the cells with yellow.

Sub ColorHyperlinks()
    'Original SelectHyperlinks: D.McRitchie 2001-01-24  buildtoc.htm
    'Modified to ColorHyperlinks: DerbyDad03 2012-10-04
    Dim hl As Variant
    Dim rng1 As String
    For Each hl In ActiveSheet.Hyperlinks
        rng1 = rng1 & "," & hl.Parent.Address(0, 0)
    Next hl
    If rng1 = "" Then
      MsgBox "No Hyperlinks found in Sheet"
      Exit Sub
    End If
    rng1 = Right(rng1, Len(rng1) - 1)
    ActiveSheet.Range(rng1).Interior.ColorIndex = 6
   End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question