+ Reply to Thread
Results 1 to 10 of 10

Override conditional formatting (in general, without changing the conditional formatting)

  1. #1
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Override conditional formatting (in general, without changing the conditional formatting)

    Hi all,

    I created a little function today that flashes a cell a certain colour. I call this, for example, when code makes changes to a cell so that we can see updated cells.

    However it runs into an issue where there is conditional formatting on the target cell(s). The interior colour of the cell (quite rightly) doesn't override the conditional formatting. Is there a solution to get my function to temporarily override any conditional formatting cell fills while it flashes the cells?

    Code below, sample attached. Thanks in advance for any help/clues/solutions.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Design everything to be as simple as possible, but no simpler.

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Override conditional formatting (in general, without changing the conditional formatti

    Hello Stormin',

    By employing another Cell and use that within your Conditional Formatting Rule it is possible as demonstrated in the attached sample Workbook. We don't bother with the Functions in place, but rather change this part of your Code;

    Please Login or Register  to view this content.
    Regards.
    Attached Files Attached Files
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Override conditional formatting (in general, without changing the conditional formatti

    Hi Stormin',

    If you prefer we could do it this way round.

    Please Login or Register  to view this content.
    Regards.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: Override conditional formatting (in general, without changing the conditional formatti

    Hi Winon, thanks for your reply.

    Unfortunately I want this to be a general function, and so changing the original conditional formatting (could be anything and any amount of rules) and adding values somewhere in the sheet is a no-no, as per thread title.

    With a fresh brain this morning I did some more digging around and found this article:
    http://excelunplugged.com/2014/06/03...ng-on-and-off/

    Putting this method into my code seems to work for everything I have thrown at it so far. See attached.

    Please Login or Register  to view this content.
    Marking thread as solved
    Attached Files Attached Files

  5. #5
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Override conditional formatting (in general, without changing the conditional formatti

    Sorry Stormin',

    Your sample Workbook does not work for me.

    Regards.

  6. #6
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: Override conditional formatting (in general, without changing the conditional formatti

    In what way? I just downloaded straight from my thread and it works for me, Excel 2016 on Win7

    flash123.png
    Last edited by Stormin'; 11-17-2017 at 11:03 AM. Reason: Added image

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Override conditional formatting (in general, without changing the conditional formatti

    HEllo Stormin',

    Thank you for the feedback.

    It could be that I am running Office 2010 on Windows 7 Professional.

    What I did notice on my side, besides the Conditional Formatting that does not work, is that every time you Run Flash it briefly shows the cells in yellow which is correct, but and the same time it adds to the Conditional Formatting Rules - "Formula = True, with no Format Settings".

    Please check if same happens on your side.
    Last edited by Winon; 11-17-2017 at 11:27 AM. Reason: Corrected OS (Operating System) - Platform

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Override conditional formatting (in general, without changing the conditional formatti

    It works in 2010 too. 2007 was notoriously buggy though.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Override conditional formatting (in general, without changing the conditional formatti

    Hi Stormin',

    Have you checked my comment;

    ...is that every time you Run Flash it briefly shows the cells in yellow which is correct, but at the same time it adds to the Conditional Formatting Rules - "Formula = True, with no Format Settings".
    @ xlnitwit,

    Did you maybe checked it as well?

    I have removed the restore CF from the Function and placed it in the run Flash Module, and that seems to solve the issue. Well from my side, at least.

    Please try the revised sample attachment!

    Regards.
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: Override conditional formatting (in general, without changing the conditional formatti

    Hi Winon,

    Sorry, I hadn't been back to check the forum lately.

    In regards to your comment
    ...is that every time you Run Flash it briefly shows the cells in yellow which is correct, but at the same time it adds to the Conditional Formatting Rules - "Formula = True, with no Format Settings".
    this is part of the design.

    So the method I used to 'override' any current conditional formatting (CF) is described in the link I provided, I just perform it programmatically and then reset everything once I'm done. Here are the stages:
    1. Add a new CF rule to target cells, with formula "=True" and no format settings. This rule is added to the end of the CF list.
    2. Turn the "StopIfTrue" setting on for this new CF, so that no more CF rules run after this rule is activated.
    3. Set the new CF to first priority / first in the list, so that all other CF rules do not run. Since "=True" is always true, target cells are set to unformatted.
    4. Flash the target cells.
    5. Delete the new blank CF we created and moved to the beginning of the CF list, removing it's "StopIfTrue" logic, and allowing all original CF rules to run.

    While your file TestFlash3.xlsm works in this specific case, what you are actually doing is deleting all CF rules and then rebuilding the old rules one by one. The aim of this function is to be able to flash cells in any general case, without needing to know or modify the current CF rules.

    Looking at your file and how the original CF rule has been changed to "StopIfTrue", I'm thinking that on your end something is going wrong with where the new 'blank' CF rule is being added and/or moved. To debug you'll have to step through the code in TestFlash2.xlsm and check that the steps above are followed, paying close attention to which position in the list the new CF rule is created in and moved to.

    We will get to the bottom of it

+ 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] I need to override a color fill set by Conditional Formatting
    By Gene@action in forum Excel General
    Replies: 8
    Last Post: 12-06-2019, 11:15 AM
  2. Override Conditional Formatting
    By Tillymint in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-14-2015, 11:47 AM
  3. How to make the command button override the conditional formatting
    By SerraAngel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-05-2014, 04:03 PM
  4. Override conditional formatting issue
    By jpruffle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-23-2011, 08:36 PM
  5. Override conditional formatting
    By greekboyuk in forum Excel General
    Replies: 12
    Last Post: 03-02-2011, 05:40 PM
  6. How can you override cell shading using Conditional formatting?
    By mrgillus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-10-2009, 11:46 AM
  7. Prevent conditional formatting from being override
    By crapit in forum Excel General
    Replies: 1
    Last Post: 11-06-2005, 07:00 AM

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