+ Reply to Thread
Results 1 to 11 of 11

Conditional Formatting - Reversed?

  1. #1
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Conditional Formatting - Reversed?

    I came across this the other day, and remembered I had encountered it before and 'got it to work' but it still bothers me. Is the conditional formatting dialog box reversed in the order it processes the rules. Attached is a simple spreadsheet with four sheets, each with the same rule. Column A has the CF applied. Column B is what I am expecting. So, using the first sheet 'Blue Then Green' as an example, my CF rules are: Top rule, if the row is odd then shade it blue. Secondary rule is that if the cell contains a 'b' then shade the cell green. I would think it would first go through and shade the odd rows, and then the second rule would overwrite any odd row that had a 'b' in it as green. That is not what I am seeing.

    Curious if others see this (maybe some weird setting in my Excel) or if they can explain my misinterpretation of 'Rule (applied in order shown)' Excel states in the CF dialog box.
    Attached Files Attached Files
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,095

    Re: Conditional Formatting - Reversed?

    Which worksheet, Pauley? I'm not sure where you are expecting us to look and comment. At first glance, I don't see anything untoward, but then I may not be seeing what you are seeing, so for this, a screenshot would help.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    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.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,652

    Re: Conditional Formatting - Reversed?

    The first rule is the "primary" rule & takes precedent over all others.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,903

    Re: Conditional Formatting - Reversed?

    It's the descriptions you've given to the first two sheets' tab names that's inconsistent.

    For the expected result the first should be named "Green then Blue", and the 2nd "Blue then Green".
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Conditional Formatting - Reversed?

    I realized that if my system is messed up, you all may see something different. I have added to column C what the CF looks like to me.

    @Fluff13, that may be the problem. The dialog box states the rules are applied in the order shown. So, my interpretation is that the top rule is applied, followed by the one below it, and so on until the bottom is reached. Really it seems like if the top rule is the primary rule, then the effect is that the rules are applied from the bottom up. Or, if top down, then anything that the top rule affects is then no longer able to be changed (but, to me, that is what the 'Stop if True' flag is supposed to do).

    @Richard, the names on the tabs are the order of the rules in the CF, not what I am actually seeing. So, in the 'Blue then Green' sheet, the rule to change the odd rows blue is the top rule and then the rule to change the 'b' cells green is the rule below it.

    Thanks,
    Paul
    Attached Files Attached Files

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Conditional Formatting - Reversed?

    Yep, just switch the order of the rules.

    They are applied from the bottom to the top.

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,652

    Re: Conditional Formatting - Reversed?

    I would agree that it is slightly misleading when it says "Applied in the order shown"
    But that is the order of priority.

  8. #8
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Conditional Formatting - Reversed?

    Okay, thanks gang. I could deduce what it was doing, but the whole 'order shown' was throwing me off. But that just adds another wrinkle to my understanding of this dialog box... the 'Stop if True' check box. I don't see how it helps. I have now added a fifth sheet called 'If True Test' and there is no setting I can come up with with the 'Stop If True' check boxes to make cell A1 any color other than yellow (assuming we keep the value in A1 as 5).

    The first rule checks if B1 is empty. If it is, then B1 is gray. I added this one to see if the 'Stop if true' stopped processing all rules below it. Nope (and this is what I would hope for) - the CF then checks A1. My expectation is that CF will stop applying formatting changes only for the affected cells and would continue to format other cells, and that seems to be what is happening.

    Now, the final three rules all affect A1, and with the value of 5 in A1, they are all true. Changing the 'Stop If True' boxes has no effect. So, it is not truly going from the bottom to the top; otherwise, checking the last rule should make cell A1 green (but it doesn't).

    So it does appear that the top rule is the 'priority' rule - but then I'm not quite sure how to use 'Stop if true'.
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,652

    Re: Conditional Formatting - Reversed?

    Change the format for A1>0 remove the fill colour & replace it with a font colour.
    Then toggle stop if true for the first rule & see what happens.

  10. #10
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Conditional Formatting - Reversed?

    @Fluff13 - thanks for going down the rabbit hole with me. I think I now have a grip on the real rules used for CF.
    - It goes top down
    - If the format attribute (e.g. the background color) for a cell hasn't previously been altered by CF, then it will make the format change; otherwise, it will not modify
    - If 'Stop if True' is set, then it will stop any further format changes to that cell

    My incorrect interpretation did not include that second step.

    Pauley

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,652

    Re: Conditional Formatting - Reversed?

    Yup that's the idea
    Thanks for the Rep

+ 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] Override conditional formatting (in general, without changing the conditional formatting)
    By Stormin' in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-21-2017, 07:15 AM
  2. [SOLVED] Get dates reversed
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-11-2015, 06:04 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. Operators reversed
    By jacksonville in forum Excel General
    Replies: 2
    Last Post: 12-14-2009, 06:57 PM
  5. Reversed Conditional Formating
    By Sean Anderson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-10-2007, 12:21 AM
  6. vlookup reversed
    By market man in forum Excel General
    Replies: 2
    Last Post: 02-07-2007, 10:56 AM
  7. [SOLVED] reversed columns
    By Vulcan in forum Excel General
    Replies: 2
    Last Post: 06-29-2005, 10:05 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