+ Reply to Thread
Results 1 to 13 of 13

Vba and conditional formatting for auto cell change

  1. #1
    Registered User
    Join Date
    11-04-2018
    Location
    DURHAM, ENGLAND
    MS-Off Ver
    2016
    Posts
    10

    Vba and conditional formatting for auto cell change

    Hi,
    I'm hoping the forum can help.

    I have an excel sheet where i need to highlight a cell with a date in it, that is 3 days past today's date, when the "staff name" cell is white (no fill).

    When the "staff name" cell is highlighted green (which turns green when i enter a hyperlink), i need the date cell to go white (no fill). I have tried different formatting and tried some VBA coding, but i cant get it to do what i need. The significance of the "green" cell is when the staff member submits their paperwork it is hyperlinked and the cell goes green.

    So, for example, cell B9 will go "red" if paperwork is outstanding after 3 days. However, when they submit paperwork, it is hyperlinked and their name will go green (i have formatting for this already). This is when i need the date cell (B9) to go back to no fill.

    I hope this makes sense
    thanks

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Vba and conditional formatting for auto cell change

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    11-04-2018
    Location
    DURHAM, ENGLAND
    MS-Off Ver
    2016
    Posts
    10

    Re: Vba and conditional formatting for auto cell change

    Re: Vba and conditional formatting for auto cell change



    Hi alansidman, thanks for explaining - i am very new to the forum so still learning. I have attached a doc with example of what i need - i hope it is understandable

    thanks
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    10-08-2018
    Location
    Lima, Peru
    MS-Off Ver
    Office 365 ProPlus
    Posts
    148

    Re: Vba and conditional formatting for auto cell change

    Create a VBA function that determines if all cells in a range are Hyperlinks

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then in conditional formatting, create a rule with the formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    image1.JPG

    This rule has to be on top of the other.

  5. #5
    Forum Contributor
    Join Date
    10-08-2018
    Location
    Lima, Peru
    MS-Off Ver
    Office 365 ProPlus
    Posts
    148

    Re: Vba and conditional formatting for auto cell change

    In case empty cells don't have to have a Hyperlink, a little change has to be in place:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-04-2018
    Location
    DURHAM, ENGLAND
    MS-Off Ver
    2016
    Posts
    10

    Re: Vba and conditional formatting for auto cell change

    Hi LeoSkywalker, i have tried the VBA and formatting, but the date cell stays red when i hyperlink both cells *see example*.
    I have used your VBA and conditional formatting, but when i hyperlink cells E9 & F9, cell B9 stays red.
    Am i missing something?
    Thanks
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    10-08-2018
    Location
    Lima, Peru
    MS-Off Ver
    Office 365 ProPlus
    Posts
    148

    Re: Vba and conditional formatting for auto cell change

    You pasted the function in the worksheet, you had to paste it in a module.

    I already moved it for you, see the file attached, alo added the line so it won't be red due to an empty cell.
    Attached Files Attached Files
    Leo Skywalker
    May the force be with you.

  8. #8
    Registered User
    Join Date
    11-04-2018
    Location
    DURHAM, ENGLAND
    MS-Off Ver
    2016
    Posts
    10

    Re: Vba and conditional formatting for auto cell change

    Quote Originally Posted by LeoSkywalker View Post
    You pasted the function in the worksheet, you had to paste it in a module.

    I already moved it for you, see the file attached, alo added the line so it won't be red due to an empty cell.
    Hi Leo Skywalker
    I have tried clearing the cells and entering data from fresh.
    Enter date, then staff names, fine - the date goes red. Hyperlink one staff name, it stays red, as it should because the other staff member hasn't submitted paperwork to be linked.

    Now, when i hyperlink the other staff member, the date stays red. But - when i close the doc, then reopen, it goes to no fill, as it should.
    Should it not do it automatically, rather than working when i close then reopen it?
    I have attached my example that i have been using for your reference. Can i say thanks again for all your help and patience, as i wouldn't have got this far without help.
    Thanks
    PhilR1
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    10-08-2018
    Location
    Lima, Peru
    MS-Off Ver
    Office 365 ProPlus
    Posts
    148

    Re: Vba and conditional formatting for auto cell change

    This happens because hyperlinking a cell does not force a recalculation, so the function does not do anything.
    You don't need to reopen the workbook to force a recalculation, there are 3 other ways to force the cell to recalculate:

    1. Press F9 key, this forces a recalculation in the workbook.

    2. Simply enter the cell you just hyperlinked and press Enter, this will force a recalculation of the cell.

    3. Making the function volatile, by adding the line right after the function declaration:

    Please Login or Register  to view this content.


    When the function is volatile, it will recalculate in most calculation events, so, it will refresh in the next movement you do.
    This has a drawback, if you have hundreds of cells, they all will recalculate at all times affecting performance a little, so it is not advisable in that situation, if this is not the case, go ahead and make the function volatile.

  10. #10
    Registered User
    Join Date
    11-04-2018
    Location
    DURHAM, ENGLAND
    MS-Off Ver
    2016
    Posts
    10

    Re: Vba and conditional formatting for auto cell change

    Hi LeoSkywalker
    The formula runs well, as previously mentioned, when i close and open the workbook.
    My original doc has staff names from a drop down list and i have changed the example to reflect this. If a new staff name is added and selected from list, the date goes red as it should.
    However, when i hyperlink it and press "enter" it doesn't recalculate - i press F9 and it does recalculate, so this is fine to use. I could not get the "volatile" code to work, but as you mentioned, the doc i have has hundreds of names on it, so it might not be worth using.

    My next question, relating to the same, is if i select the name from the drop down (the date goes red correctly) can the name be made to red, in line with the date (and then obviously change with hyperlink). I have attached my example for your ref.

    Thanks

    PhilR1
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    10-08-2018
    Location
    Lima, Peru
    MS-Off Ver
    Office 365 ProPlus
    Posts
    148

    Re: Vba and conditional formatting for auto cell change

    Hi Phil,

    You have created a new second AllHyperlink function, that's why Excel pops an "Ambiguous name" message.
    The volatility line has to be added to the original function, not duplicate it.

    The code should loook like this:

    Please Login or Register  to view this content.
    Having said that, I loooked at it for some time, Excel does format a cell automatically with green background and blue underline, but it wont revert back to plain if you delete the contents of the cell, this Excel autoformat is somehow conflicting with your new requirement.

    Also, you say there are hundres of lines, yet you just provide only one row in your example worksheet.

  12. #12
    Registered User
    Join Date
    11-04-2018
    Location
    DURHAM, ENGLAND
    MS-Off Ver
    2016
    Posts
    10

    Re: Vba and conditional formatting for auto cell change

    Quote Originally Posted by LeoSkywalker View Post
    Also, you say there are hundres of lines, yet you just provide only one row in your example worksheet.
    Hi Leo,

    It is a large worksheet i have created as we have about 150 staff. So, if you can picture 10 columns for staff names with drop down boxes for all their names, i thought
    it better to create a "short" example, rather than attaching the full worksheet.

    Still not working automatically but i can get away with using F9, so thanks very much for your help - i couldn't have done it without your help LeoSkywalker.

  13. #13
    Registered User
    Join Date
    11-04-2018
    Location
    DURHAM, ENGLAND
    MS-Off Ver
    2016
    Posts
    10

    Re: Vba and conditional formatting for auto cell change

    Hi LeoSkywalker

    Now i have added it to my worksheet, it is working perfect - thanks ever so much for your help. Is there a way of making the "staff name" highlighted when the date highlights, e.g. date goes red and staff name goes red, until hyperlinked?
    I have tried with conditional formatting, but cant get it to work and i dont want to mess with the code you gave me
    Thanks

    Phil

+ 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. [SOLVED] Conditional Formatting to change the style of the cell
    By hanif in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-25-2017, 10:05 AM
  2. [SOLVED] Conditional Formatting change if cell changes
    By shaz0503 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2015, 10:21 PM
  3. conditional formatting value change of same cell.
    By tcalhoun in forum Excel General
    Replies: 5
    Last Post: 04-24-2013, 01:09 AM
  4. Conditional Formatting via VBA: Change formatting in range based on value of each cell
    By ralphjmedia in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2013, 10:37 AM
  5. Conditional Formatting based on change in one cell
    By Jadvancing in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-28-2013, 06:45 PM
  6. Conditional Formatting Help-cell to change to re
    By tim1218 in forum Excel General
    Replies: 4
    Last Post: 06-22-2009, 03:01 PM
  7. Conditional Formatting to Auto Fill Cell
    By fmluder93 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-26-2009, 06:18 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