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
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
Highlighted
Thanks for the reply Kenton
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 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
13 Replies