Conditional format if hyperlink
Solved/Closed3 replies
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
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
Create a function to check if the cell is a hyperlink:
e.g.
=IsHyperLink(A1)
You can use link any of the built-in functions.
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.
Obonden
Posts
7
Registration date
Saturday March 25, 2017
Status
Member
Last seen
April 24, 2017
1
Apr 23, 2017 at 03:43 PM
Apr 23, 2017 at 03:43 PM
Bingo! The "Public Function" solved my trouble! Thanks a lot!
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.
Give that a try.
Obonden
Posts
7
Registration date
Saturday March 25, 2017
Status
Member
Last seen
April 24, 2017
1
Apr 21, 2017 at 06:38 PM
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"?
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"?
Apr 22, 2017 at 01:26 AM
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.
Apr 24, 2017 at 04:41 PM
Apr 24, 2017 at 04:49 PM
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.
Updated on Apr 24, 2017 at 05:04 PM
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 :-)
Apr 24, 2017 at 05:08 PM