SOLVED
Home

Hyperlinked cell - auto change colour

%3CLINGO-SUB%20id%3D%22lingo-sub-227880%22%20slang%3D%22en-US%22%3EHyperlinked%20cell%20-%20auto%20change%20colour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227880%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20when%20i%20attach%20a%20hyperlink%20to%20a%20cell%2C%20i%20want%20it%20to%20auto%20change%20colour.%20I%20have%20used%20Conditional%20Formatting%20for%20%22No%20Blank%22%2C%20but%20this%20works%20with%20any%20text%20-%20i%20want%20the%20cell%20to%20only%20format%20when%20a%20Hyperlink%20is%20attached%20to%20it.%20I%20would%20really%20appreciate%20some%20help.%3C%2FP%3E%3CP%3EThanks%20in%20advance%20all.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-227880%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-239122%22%20slang%3D%22en-US%22%3ERe%3A%20Hyperlinked%20cell%20-%20auto%20change%20colour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239122%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20reply%20Kenton%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-239121%22%20slang%3D%22en-US%22%3ERe%3A%20Hyperlinked%20cell%20-%20auto%20change%20colour.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-239121%22%20slang%3D%22en-US%22%3EThanks%2C%20now%20i%20see%20it%20-%20it%20is%20easy!%3CBR%20%2F%3EThanks%20Michael%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-227986%22%20slang%3D%22en-US%22%3EHyperlinked%20cell%20-%20auto%20change%20colour.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227986%22%20slang%3D%22en-US%22%3E%3CP%3EA%20hyperlink%20has%20a%20format%2C%20in%20the%20same%20way%20that%20headings%20are%20formats.%20Assuming%20you're%20using%20a%20version%20with%20a%20ribbon%3B%20start%20from%20your%20hyperlinked%20cell%2C%20click%20the%20Home%20ribbon%20tab%2C%20then%20cell%20styles.%20In%20the%20second%20section%2C%20%22Data%20and%20Model%2C%22%20you%20will%20see%20the%20Hyperlink%20style.%20Right-click%20on%20it%2C%20and%20you%20can%20edit%20the%20style%20to%20be%20as%20you%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-227981%22%20slang%3D%22en-US%22%3ERe%3A%20Hyperlinked%20cell%20-%20auto%20change%20colour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-227981%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20your%20workbook%20create%20a%20VBA%20module.%20Add%20the%20following%20code%20to%20it%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFunction%20IsHyperlink(Cell%20As%20Range)%20As%20Boolean%3CBR%20%2F%3E%26nbsp%3B%20If%20Cell.Hyperlinks.Count%20%26gt%3B%200%20Then%3CBR%20%2F%3E%26nbsp%3B%20IsHyperlink%20%3D%20True%3CBR%20%2F%3E%26nbsp%3B%20Else%3A%20IsHyperlink%20%3D%20False%3CBR%20%2F%3E%26nbsp%3B%20End%20If%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20this%20IsHyperlink%20function%20in%20your%20Conditional%20Formatting%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
PHILIP ROCHESTER
Occasional Contributor

Hi, when i attach a hyperlink to a cell, i want it to auto change colour. I have used Conditional Formatting for "No Blank", but this works with any text - i want the cell to only format when a Hyperlink is attached to it. I would really appreciate some help.

Thanks in advance all.

4 Replies

In your workbook create a VBA module. Add the following code to it:

 

Function IsHyperlink(Cell As Range) As Boolean
  If Cell.Hyperlinks.Count > 0 Then
  IsHyperlink = True
  Else: IsHyperlink = False
  End If
End Function

 

Use this IsHyperlink function in your Conditional Formatting  

 

 

Solution

A hyperlink has a format, in the same way that headings are formats. Assuming you're using a version with a ribbon; start from your hyperlinked cell, click the Home ribbon tab, then cell styles. In the second section, "Data and Model," you will see the Hyperlink style. Right-click on it, and you can edit the style to be as you want.

 

Thanks, now i see it - it is easy!
Thanks Michael
Thanks for the reply Kenton
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies