+ Reply to Thread
Results 1 to 8 of 8

Get Conditional Formatting to stick with Followed Hyperlinks

  1. #1
    Registered User
    Join Date
    12-31-2019
    Location
    Denver, CO
    MS-Off Ver
    365 ProPlus
    Posts
    4

    Question Get Conditional Formatting to stick with Followed Hyperlinks

    I am attempting to build a form within Excel that requires hyperlinks in a dropdown format. I know this can only be done with the HYPERLINK formula. But I only want it to appear visible when something is selected in the drop down so I have a conditional format that makes the text WHITE when the field is blank. This works until the link is clicked. Once it's clicked, its like the conditional formatting ceases to work. How can I get Conditional Formatting to work after the link is clicked.
    Attached Files Attached Files
    Last edited by jespo124; 01-02-2020 at 12:13 PM. Reason: Adding attachment

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,049

    Re: Get Conditional Formatting to stick with Followed Hyperlinks

    Welcome to the forum.

    I'm struggling to visualise this. Please attach a sample workbook - instructions at the top of the page.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    12-31-2019
    Location
    Denver, CO
    MS-Off Ver
    365 ProPlus
    Posts
    4

    Re: Get Conditional Formatting to stick with Followed Hyperlinks

    Attachment has been added. The end user experience should be
    1. Pick a catagory in cell A5
    2. Pick a drop down item in grey boxes A12, C12 or E12
    3. A VLookup function in cell A13, C13 or E13 pulls in the link data from Sheet 2
    4. The end user sees text in A14, C14, or E14 with a link (HYPERLINK function) directing them to click to get the content

    The last step is the one with the conditional formatting that is the grey boxes are empty that the text in A14, C14 and E14 should be white (so the user can't see it).
    In my attachment you can see tha to be true for E14 because the link has never before been clicked but A14 and C14 are still showing blue links because they have been clicked before.

    Does this help?

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,269

    Re: Get Conditional Formatting to stick with Followed Hyperlinks

    In my attachment you can see tha to be true for E14 because the link has never before been clicked but A14 and C14 are still showing blue links because they have been clicked before.
    Cells A14, C14, and E14 are all blank.
    Furthermore the data validation in cells A12, C12 and E12 does not work so it is hard to replicate the issue.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    12-31-2019
    Location
    Denver, CO
    MS-Off Ver
    365 ProPlus
    Posts
    4

    Re: Get Conditional Formatting to stick with Followed Hyperlinks

    So cells A14, C14, and E14 are intended to be blank when the document loads for the first time. And the data validation in cells A12, C12 and E12 is reliant on A5 being picked.

    If you pick Sample 1 in cell A5; A12 can be dropped down to Facebook which loads a link into A13 and then A14 brings up the text 'Go to Einstein Document listed'
    After you click on A14 and it takes you to the website - click Reset Tool and you'll notice that A14 is no longer blank (it shows a link of #N/A)
    It's this action (showing a link of #N/A) that I am attempting to fix because the conditional formating I have built in doesn't seem to be working once the link is followed.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,269

    Re: Get Conditional Formatting to stick with Followed Hyperlinks

    I used the following formula as a conditional formatting rule applied to cell A13: =A12=""
    I set the font to white as you mentioned in post #1 and it hid the #N/A once reset tool button was selected.
    I might mention that you could also accomplish displaying a blank in cell A13, after resetting, by modifying the formula to read: =IFERROR(VLOOKUP(A12,Sheet2!B:C, 2, 0),"")
    Let us know if you have any questions.

  7. #7
    Registered User
    Join Date
    12-31-2019
    Location
    Denver, CO
    MS-Off Ver
    365 ProPlus
    Posts
    4

    Re: Get Conditional Formatting to stick with Followed Hyperlinks

    Thanks for looking at it JeteMC. I'm not really worried about how A13 looks as it is generally a hidden cell - my concern is getting A14 to go back to being white once the link is selected. I tried your suggestion using conditional formatting on A14 with the formula of '=A12=""' but it still shows the link when the form is reset. Am I missing something?

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,269

    Re: Get Conditional Formatting to stick with Followed Hyperlinks

    I feel as if I missed what you wanted, hopefully I understand now.
    Try modifying the formula in A14 to read: =IFERROR(HYPERLINK(A13, "Go to Einstein Document listed"),"")
    Note that this works after cell A17 is selected.
    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Hyperlinks and conditional formatting
    By Lev1948 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-05-2016, 06:53 AM
  2. [SOLVED] How to Make Custom Formatting Stick
    By AliGW in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 11-02-2015, 07:57 AM
  3. Opening xlsm files with conditional formatting opens with removed conditional formatting
    By Martijn.Steenbakker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2014, 05:38 AM
  4. Find Hyperlinks, Copy Hyperlinks to alternative sheet, print all hyperlinks
    By matrixpom in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2013, 05:13 PM
  5. [SOLVED] Conditional Format with Icons - rules won't "stick"
    By portlandjackie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2012, 02:00 PM
  6. Conditional Hyperlinks?
    By Mhz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-24-2007, 02:48 AM
  7. [SOLVED] Make pivotchart formatting stick after pivottable refresh.
    By larry garka in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-16-2006, 02:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1