Conditional format if hyperlink
Solved/Closed
Related:
- Excel hyperlink color
- Hyperlink in gmail app - Guide
- Excel hyperlink to another sheet - Guide
- Excel mod apk for pc - Download - Spreadsheets
- Html text color - Guide
- Color 65535 - Guide
3 responses
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
5
Registration date
Saturday 25 March 2017
Status
Member
Last seen
24 April 2017
1
23 Apr 2017 à 15:43
23 Apr 2017 à 15:43
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
5
Registration date
Saturday 25 March 2017
Status
Member
Last seen
24 April 2017
1
21 Apr 2017 à 18:38
21 Apr 2017 à 18:38
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"?
22 Apr 2017 à 01:26
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.
24 Apr 2017 à 16:41
24 Apr 2017 à 16:49
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.
24 Apr 2017 à 17:04
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 :-)
24 Apr 2017 à 17:08