Conditional formatting criterion

[harry888]

I have a macro that creates a hyperlink anchored to some text, using a variable named "URL", like this.

URL = "[URL]http://www.mydomain.com/mypage?parameter[/URL]=" & [A1]
ActiveSheet.Hyperlinks.Add Anchor:=[C3], _
    Address:=URL, TextToDisplay:="MyText"

I want to use conditional formatting to highlight a cell if URL has a particular value; e.g., if URL = "http://www.mydomain.com/mypage?parameter=" (i.e., [A1] = null.)
I tried using this ...

=URL="[URL]http://www.mydomain.com/mypage?parameter="[/CODE[/URL]]
... as the conditional formatting formula, but it didn't work. How do I make this work?

 

[pgc01]

Hi Harry
Welcome to the board

I don't know any excel native worksheet function that gives you the hyperlink address.
You'll have to write a udf to extract the hyperlink in the cell and use it in the conditional formatting formula.

 

[harry888]

Thanks for your reply. I don't know how to write a udf (user-defined function?), but is it necessary to extract the hyperlink given that it is already set in the variable "URL"? Is it not possible just to use the variable in the conditional formatting formula?

 

[pgc01]

Thanks for your reply. I don't know how to write a udf (user-defined function?), but is it necessary to extract the hyperlink given that it is already set in the variable "URL"? Is it not possible just to use the variable in the conditional formatting formula?

Hi

Sorry, unless you have defined some name yourself I see no variable, I just see 3 letters, URL.

If you want to use a UDF, this is an example.

Paste in a standard module:

Function GetUrl(r As Range) As String
If r.Count = 1 Then If r.Hyperlinks.Count > 0 Then GetUrl = r.Hyperlinks(1).Address
End Function

Then, for ex. select C1:C10 and in the conditional formatting use the formula:

=GetUrl(C1)="http://www.mydomain.com/mypage?parameter="&$A$1

and set the highlighting format.

With the code you posted in post #1 , C3 should be highlighted