Menu
Ask a question Report

Conditional format if hyperlink [Solved/Closed]

Obonden - Apr 20, 2017 at 05:03 PM - Latest reply: ac3mark 9454 Posts Monday June 3, 2013Registration dateModeratorStatus June 7, 2018 Last seen
- Apr 24, 2017 at 05:08 PM
Excel Ask a question Report
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 :-))


See more 

12 replies

Best answer
Report Respond to x
x - Apr 22, 2017 at 05:48 PM
1
Thank you
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.

Thank you, x 1

Something to say? Add comment

CCM has helped 1983 users this month

Report
x - Apr 22, 2017 at 05:51 PM
Couldn't edit:
You can use like any of the built-in functions.
Report
Obonden 7 Posts Saturday March 25, 2017Registration date April 24, 2017 Last seen - Apr 23, 2017 at 03:43 PM
Bingo! The "Public Function" solved my trouble! Thanks a lot!
Report Respond to ac3mark
ac3mark 9454 Posts Monday June 3, 2013Registration dateModeratorStatus June 7, 2018 Last seen - Apr 21, 2017 at 01:50 PM
0
Thank you
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.
Report
Obonden 7 Posts Saturday March 25, 2017Registration date April 24, 2017 Last seen - 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"?
Report Respond to ac3mark
ac3mark 9454 Posts Monday June 3, 2013Registration dateModeratorStatus June 7, 2018 Last seen - Updated by ac3mark on 21/04/17 at 07:22 PM
0
Thank you
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
View all 6 comments
Report
ac3mark 9454 Posts Monday June 3, 2013Registration dateModeratorStatus June 7, 2018 Last seen - 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!
Report
Obonden 7 Posts Saturday March 25, 2017Registration date April 24, 2017 Last seen > ac3mark 9454 Posts Monday June 3, 2013Registration dateModeratorStatus June 7, 2018 Last seen - 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.
Report
ac3mark 9454 Posts Monday June 3, 2013Registration dateModeratorStatus June 7, 2018 Last seen - Apr 24, 2017 at 04:53 PM
SO, your scope has changed. Your OP says to look and change to a HYPERLINK! If you cannot take my example, and make it work for you, then tell your boss you can't make it work!

This is the #1 reason I will not provide a turn key solution. Once again, if you cannot take my example and apply it to your model, then you are stuck. Maybe someione else will come along and give you the solution, but as far as I am concerned, this is solved with the example I posted!!

Have FUN!
Report
Obonden 7 Posts Saturday March 25, 2017Registration date April 24, 2017 Last seen - 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 :-)
Report
ac3mark 9454 Posts Monday June 3, 2013Registration dateModeratorStatus June 7, 2018 Last seen - 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!
Ask a question