Conditional Formatting - if there is hyperlink

[rexwrx]

Hi again everyone,

I've posted this similar question, but it's not on hyperlink.

Anyway, i want to check in a given cell, if I put a hyperlink, i want it to be GREEN in colour, and if i remove the hyperlink, it returns to YELLOW.

Can I do this with UDF, via VBA?

cheers

 

[PaddyD]

how are you setting up the hyperlink? via a hyperlink() formula, or more directly?

 

[rexwrx]

just using right-click and "Add Hyperlink".

Whichever way is easier, i'm open to suggestions.

 

[rexwrx]

any ideas??

can anyone help me out here please?

 

[giacomo]

you could create an hasHyperlink() UDF and then use conditional formatting to change the color of the cell. Example:

Public Function hasHyperlink(ref As Range) As Boolean
   hasHyperlink = ref.Hyperlinks.Count > 0
End Function

hth,
Giacomo

 

[rexwrx]

yep, essentially that's what i've done whilst waiting for a reply.
If rng.Hyperlinks.Count > 0 Then
HasLink = True
If Len(HasLink) > 0 Then
'HasLink = rng.Hyperlinks(1).SubAddress
HasLink = True
'rng.HorizontalAlignment = xlCenter
'rng.Interior.ColorIndex = 36
'rng.Interior.Pattern = xlSolid
End If
Else
HasLink = False
' rng.HorizontalAlignment = xlCenter
' rng.Interior.ColorIndex = 36
' rng.Interior.Pattern = xlSolid

End If

and yes it works with conditional formatting.

However, when i remove the link, the cell becomes white, and the format returns to the standard format (left aligned)

I need it to return to the center aligned and with light yellow background with border around the cells

does the code above look right? especially the part where i tried to "FORMAT" the cells???