+ Reply to Thread
Results 1 to 3 of 3

Conditional Formatting issue

  1. #1
    Registered User
    Join Date
    07-03-2019
    Location
    Stockholm, Sweden
    MS-Off Ver
    2013
    Posts
    61

    Conditional Formatting issue

    Hi,

    I have a conditional formatting which is causing me a headache. Using formulas, I want the cells to be highlighted in certain colours based on certain conditions, a bit tricky to explain but attached file shows an example.

    One rule is that if the overall value ("middle cell") is lower than 1%, the fields should be whitened (as this small share is not relevant to look at). It works fine when total is 0% (and all related values are "DIV/0!"). However, if the total is between 0 and 1 but all related values equal zero, it does not work any more. I think it has to do with the other rules eliminating each other but I don't know what to do. I tried changing the order of the conditions but can't get it to turn white...

    Any suggestions??

    Additional comment: Just realised that the same "error" occurs when not all "realted values" are zero. Then colour is chosen according to which is the largest although it should be white due to the total being below 1%...

    Big thanks already!!

    Wiebke
    Attached Files Attached Files
    Last edited by whahnelt; 10-07-2019 at 05:07 AM.

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Conditional Formatting issue

    Hi

    First rule must be $C$7<1% (format as blank)

    For the cells in the corners use this formula and adapt as you do in your example.
    =AND(MAX($B$6,$D$6,$B$8,$D$8)=$B$6,$B$6>0) (Blue)
    =AND(MAX($B$6,$D$6,$B$8,$D$8)=$D$6,$D$6>0) (Dark blue)
    =AND(MAX($B$6,$D$6,$B$8,$D$8)=$B$8,$B$8>0) (Black)
    =AND(MAX($B$6,$D$6,$B$8,$D$8)=$D$8,$D$8>0) (Red)

    See if that helps you.
    Note: In first rule you must check Stop if true.
    Last edited by José Augusto; 10-07-2019 at 04:55 AM. Reason: Add a note

  3. #3
    Registered User
    Join Date
    07-03-2019
    Location
    Stockholm, Sweden
    MS-Off Ver
    2013
    Posts
    61

    Re: Conditional Formatting issue

    The order and "Stop if true" did the trick - so logical, duh... I don't even have to change the other formulas (which is lucky as this is applied for quite a large range...)

    Huge thanks for your quick help!!!

    W.

+ 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 issue
    By zookeepertx in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-26-2014, 01:37 PM
  2. Custom formatting issue using conditional formatting
    By wrongway15 in forum Excel General
    Replies: 2
    Last Post: 07-13-2014, 03:18 PM
  3. VBA Conditional Formatting Issue
    By maddog9486 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-08-2014, 11:21 AM
  4. Conditional Formatting Issue
    By Tailoredcs in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-11-2013, 02:29 PM
  5. Conditional Formatting Issue
    By netcat17 in forum Excel General
    Replies: 6
    Last Post: 04-05-2010, 07:57 PM
  6. Conditional Formatting Issue
    By Gavin Ling in forum Excel General
    Replies: 3
    Last Post: 05-12-2007, 10:13 AM
  7. [SOLVED] conditional formatting issue
    By QUESTION-MARK in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2006, 08:10 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