Previous Topic Next Topic

Conditional format if hyperlink

Solved/Closed
Obonden - Apr 20, 2017 at 05:03 PM
 Blocked Profile - Apr 24, 2017 at 05:08 PM
Hello,
In my workbook I have conditional format background color is green if there are a certain value in the cell. The text in the cell has font color "automatic" = black. If I add a hyperlink to the cell, the text is underlined, but font color still is black, not blue as in the definition of style "Hyperlink". I want the text to be blue (and underlined). Could any please help me how to get around this? Are there any formula like "= ishyperlink(A1)"? (I tried that one. Didn't get any errormessage, but it didnt work :-))


Related:

3 responses

Answer 1 / 3
Blocked Profile
Updated on Apr 21, 2017 at 07:22 PM
There is a rule set already built in. Take a look at the below example.




This will look for any text with the word SELECTED in it. Notice the "*".



It's kind of fun to do the impossible! -Walter Elias Disney
1
Obonden Posts 5 Registration date Saturday March 25, 2017 Status Member Last seen April 24, 2017 1
Apr 22, 2017 at 01:26 AM
Hi, and thanks again. I really appreciate you try to help me!
I'm afraid I didn't explain good enough. My cell content is a date - not the hyperlinktext itself. I tried your solution, but couldn't get it to work. I guess the reason is my cell content.

What I think is strange is that when I apply a hyperlink to the cell, the text gets underlined. It is "on it's way" to be formated as hyperlink text usually it, but the blue color is missing. (The reason I stress this is that the underlining is not visible enough, so I would like the blue text in addition.) Another strange thing is that if I follow the link, the format in the cell changes to the format for "followed links".

(I have made an example, but I couldn't find out how to insert images here. You will find a shared image here: https://drive.google.com/file/d/0B4bxs4o1wkomaElDa1F0VGlDX0E/view?usp=sharing
In that image cell A1, B3 and C5 is followed links.
In cell A2, B4 and C6 there are links which not have been followed.
0
Blocked Profile
Apr 24, 2017 at 04:41 PM
Did you understand my example? If not, then I am not certain not matter what you have posted, I can explain it right. I DO NOT provide cut and paste solutions, but try to post a very STURDY framework on which to build your solution!
-3
Obonden Posts 5 Registration date Saturday March 25, 2017 Status Member Last seen April 24, 2017 1 > Blocked Profile
Apr 24, 2017 at 04:49 PM
Hi
May be I didn't understand your example, but as written in my comment above I didn't succeed making a "Format only cells that contain"-rule. Of what reason I don't know, but it might be because my cell information is a text (date), and not the hyperlinktext itself.
0
Obonden Posts 5 Registration date Saturday March 25, 2017 Status Member Last seen April 24, 2017 1
Updated on Apr 24, 2017 at 05:04 PM
Hi
You sounds like being angry at me? You shouldn't. I am very glad you made an effort solving my trouble, and I am sorry I didn't explain my problem sufficiently. It seems my problems is solved by using the Public Function defining a IsHyperlink-element. I really do not understand what this code is doing - but doesn't matter for me as long as it works, and I don't need to tell my boss I couldn't solve the problem. I surely have fun :-)
1
Blocked Profile
Apr 24, 2017 at 05:08 PM
IsHyperlink returns a True or False. It tests whether the text is constructed as a hyperlink. Take my example, and apply it to your model. The word SELECTED in my example, is what I was looking for. So, if you are looking for "How Now Brown Cow", then place that string into your formula. Let me know if that helps!
0
Answer 2 / 3
x
Apr 22, 2017 at 05:48 PM
Create a function to check if the cell is a hyperlink: 

Public Function IsHyperlink(aRng As Range) As Boolean
    IsHyperlink = CBool(aRng.Hyperlinks.Count)
End Function


e.g.
=IsHyperLink(A1)

You can use link any of the built-in functions.
1
x
Apr 22, 2017 at 05:51 PM
Couldn't edit:
You can use like any of the built-in functions.
0
Obonden Posts 5 Registration date Saturday March 25, 2017 Status Member Last seen April 24, 2017 1
Apr 23, 2017 at 03:43 PM
Bingo! The "Public Function" solved my trouble! Thanks a lot!
0
Answer 3 / 3
Blocked Profile
Apr 21, 2017 at 01:50 PM
Ok, write another rule that checks the value of the cell, not just if there is one. Change the cell text format to change to blue if the text contains a value like: "http://".

Give that a try.
0
Obonden Posts 5 Registration date Saturday March 25, 2017 Status Member Last seen April 24, 2017 1
Apr 21, 2017 at 06:38 PM
Hi
Thanks, but I'm not able to set this up:
I really feel like an amateur when I have to ask what operator is used for "contains"? I have browsed for it, but have not been able to find it. According to your suggestion the formula should be like = A1 = "http://", but that expression would only be True when the cell content is exactly like "http://" (and nothing more), as "=" operator means "like", and not "contains"?
0