Excel - How to Revert Followed Links to Their Original Font Color

SYSTEM
MS Excel 2013
MS Windows 7

BACKGROUND
One column of the spreadsheet contains hyperlinks. When I click on a hyperlink, the browser opens to that link and the color of the cell's text changes to reflect that it has been clicked on already, or "followed".

QUESTION
I would like to be able to select one cell or many and revert them to the original font color before the link was followed. Can this be done? Also, is there a way to keep the color from changing for the specific spreadsheet?
Jerry LOperations ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Echo_SCommented:
I don't know about reverting, but to keep the followed hyperlink color from changing, you can adjust your color scheme and make the hyperlink and followed hyperlink colors the same.

In Excel 2013, go to the Page Layout tab and click Colors, then choose Customize Colors at the bottom of the gallery. In the Create New Theme Colors dialog, change the Followed Hyperlink to match the Hyperlink color. (You can change both if you want.) Name your custom color theme and click OK.

Create New Theme Colors dialog
It will be automatically applied to your file. You can tell what color theme is in use by hovering over the Colors button on the Page Layout tab. The name of the color theme will appear in a tool tip.

You know, this will take care of changing the colors on your existing file, so it may not matter if you actually revert the links so they're no longer "followed."
[ fanpages ]IT Services ConsultantCommented:
Hi,

This approach is a little 'crude', but it may satisfy your requirements.

The following code is within the Code Module for the Worksheet where the Hyperlinks are listed.

Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

  Dim objCell                                           As Range
  Dim objHyperlink                                      As Hyperlink
  Dim strAddress                                        As String
' Dim vntSubAddress                                     As Variant
' Dim vntScreenTip                                      As Variant
' Dim vntTextToDisplay                                  As Variant
  
  For Each objCell In Target.Parent.Cells
  
      For Each objHyperlink In objCell.Hyperlinks
      
          strAddress = objHyperlink.Address
'         vntSubAddress = objHyperlink.SubAddress
'         vntScreenTip = objHyperlink.ScreenTip
'         vntTextToDisplay = objHyperlink.TextToDisplay
          
          objHyperlink.Delete
          
          ActiveSheet.Hyperlinks.Add Anchor:=objCell, _
                                     Address:=strAddress
'                                    SubAddress:=vntSubAddress, _
                                     ScreenTip:=vntScreenTip, _
                                     TextToDisplay:=vntTextToDisplay
      
      Next objHyperlink
      
  Next objCell
  
  Set objHyperlink = Nothing
  Set objCell = Nothing
  
End Sub

Open in new window


The attached workbook has a single worksheet that contains a few example hyperlinks in column [F].
Q-28677424.xls
Jerry LOperations ManagerAuthor Commented:
@Fanpages, it looks like your solution (almost) works. Is there a way for the code to do what it's doing (keep the font color as the "unfollowed"), but not affect the cell background color, or any other formatting that might be applied to the cell (such as bold, italic, etc.)?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jerry LOperations ManagerAuthor Commented:
@Admin: My comment (above) has not been replied to.

Should I accept this solution and submit a new question about how to retain the other cell formatting?
[ fanpages ]IT Services ConsultantCommented:
What other cell formatting is applied to the cells with the hyperlinks?

Please be as specific as possible, or provide an example workbook to demonstrate the formatting applied to at least one of the cells.

Also, are all cells with hyperlinks formatted identically?
Jerry LOperations ManagerAuthor Commented:
Referring to the cells with hyperlinks:

One formatting we need to worry about is the cell "background color". Each cell can have a different background color, they are NOT all the same. Some will not have any background color.

Some cells may have "bold" text formatting, but others will be the default font settings.

All other formatting for cells with hyperlinks can be assumed to be whatever the sheet defaults are.
[ fanpages ]IT Services ConsultantCommented:
So, just "background color" & whether the text is in Bold, or not?

If that is the case...

Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

' Q_28677424 - fanpages (21 May 2015 & 9 June 2015) [ http://www.experts-exchange.com/members/fanpages.html ]

  Dim blnFont_Bold                                      As Boolean
  Dim dblInterior_Color                                 As Double
  Dim dblInterior_PatternTintAndShade                   As Double
  Dim dblInterior_TintAndShade                          As Double
  Dim lngInterior_Pattern                               As Long
  Dim lngInterior_PatternColorIndex                     As Long
  Dim lngInterior_ThemeColor                            As Long
  Dim objCell                                           As Range
  Dim objHyperlink                                      As Hyperlink
  Dim strAddress                                        As String
' Dim vntSubAddress                                     As Variant
' Dim vntScreenTip                                      As Variant
' Dim vntTextToDisplay                                  As Variant
  
  On Error Resume Next
  
  For Each objCell In Target.Parent.Cells
  
      For Each objHyperlink In objCell.Hyperlinks
      
          strAddress = objHyperlink.Address
'         vntSubAddress = objHyperlink.SubAddress
'         vntScreenTip = objHyperlink.ScreenTip
'         vntTextToDisplay = objHyperlink.TextToDisplay
          
          blnFont_Bold = objCell.Font.Bold
          lngInterior_Pattern = objCell.Interior.Pattern
          lngInterior_PatternColorIndex = objCell.Interior.PatternColorIndex
          
          lngInterior_ThemeColor = 0&
          lngInterior_ThemeColor = objCell.Interior.ThemeColor                              ' Not available in MS-Excel 2003
          
          dblInterior_Color = objCell.Interior.Color
          dblInterior_TintAndShade = objCell.Interior.TintAndShade
          dblInterior_PatternTintAndShade = objCell.Interior.PatternTintAndShade
          
          objHyperlink.Delete
          
          ActiveSheet.Hyperlinks.Add Anchor:=objCell, _
                                     Address:=strAddress
'                                    SubAddress:=vntSubAddress, _
                                     ScreenTip:=vntScreenTip, _
                                     TextToDisplay:=vntTextToDisplay
      
          objCell.Interior.Pattern = lngInterior_Pattern
          objCell.Interior.PatternColorIndex = lngInterior_PatternColorIndex
          
          If lngInterior_ThemeColor = 0& Then
             objCell.Interior.Color = dblInterior_Color
          Else
             objCell.Interior.ThemeColor = lngInterior_ThemeColor
          End If ' If lngInterior_ThemeColor = 0& Then
                    
          objCell.Interior.TintAndShade = dblInterior_TintAndShade
          objCell.Interior.PatternTintAndShade = dblInterior_PatternTintAndShade
          
          objCell.Font.Bold = blnFont_Bold
          
      Next objHyperlink
      
  Next objCell
  
  Set objHyperlink = Nothing
  Set objCell = Nothing
  
End Sub

Open in new window


An updated workbook is attached.
Q-28677424-v2.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jerry LOperations ManagerAuthor Commented:
Works great, thank you.
[ fanpages ]IT Services ConsultantCommented:
You're welcome.

Good luck with the rest of your project.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.