+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting with nil value

  1. #1
    Registered User
    Join Date
    08-14-2012
    Location
    Sydney
    MS-Off Ver
    Microsoft 365
    Posts
    90

    Conditional Formatting with nil value

    Hi, With valued help from forum members, I have conditional formatting (colour fill) for a range of percentages calculated using this formula =IF(ISERROR(G104/H104),"",G104/H104). But if there is no value returned because there is no data to calculate, the conditional formatting defaults to the formula at the top of the sort order. I want to set the formula to return a neutral fill and I thought this might work =I104="" But it doesn't. I have attached a sample. Thx
    Attached Files Attached Files

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Conditional Formatting with nil value

    Add this as a new rule with no formatting to be applied.
    =LEN(I5)=0

    Add it as the first rule and Stop if true.

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Conditional Formatting with nil value

    hi there. a few things;
    1.
    But if there is no value returned because there is no data to calculate, the conditional formatting defaults to the formula at the top of the sort order
    it does not default to the formula at the top. but it checks the conditional formatting rule by sequence. in cell I14, you input the formula to show blank if G14/H14 is an error. hence, it shows a blank. Excel regards blank as something bigger than number. try this somewhere.
    =I14>9999999
    you could even put bigger numbers. it will come out TRUE
    hence, it shows you red because blank is bigger than 0.4999

    2.
    I thought this might work =I104="" But it doesn't.
    it does actually. but why I104? i am guessing it's your real file's cell reference. this file only has up till I16. you need to select from I5 to I16. go to Home tab -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
    I5=""
    not sure what your neutral fill means since there are alternate color in each row. but you can format this and it should work.

    alternatively and preferably, each of your existing conditions should include:
    =AND(I5>0.4999,I5<>"")

    firstly, the sequence won't matter now. if you added in the condition of I5<>"", you must ensure this condition is on top of the rest for it to work.
    secondly, it follows your cell fill and will be in alternate colors

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: Conditional Formatting with nil value

    To keep formatting the same as in cells on the left, czhange all forumulas in the rules in following manner:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The above is for red, but once you change it, you will notice "" cells will become orange, so use similar ammendment to other CF rules too. (see red marker on a screenshot ORAZ is equivalent to AND in my locallized version of Excel)

    To change formatting to Light gray (I don't think you ment it writing "return a neutral fil'", but just in case ...) add new rule with formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and make sure it's topmost rule (blue marker on screenshot)
    Attached Images Attached Images
    Best Regards,

    Kaper

  5. #5
    Registered User
    Join Date
    08-14-2012
    Location
    Sydney
    MS-Off Ver
    Microsoft 365
    Posts
    90

    Re: Conditional Formatting with nil value

    Thanks Kersplash, I used this and it worked.

  6. #6
    Registered User
    Join Date
    08-14-2012
    Location
    Sydney
    MS-Off Ver
    Microsoft 365
    Posts
    90

    Re: Conditional Formatting with nil value

    Thanks Benshiryo, I had a go but couldn't get it right. I ended up using Kersplash's suggestion. But thanks for helping me out again. Much appreciated.

  7. #7
    Registered User
    Join Date
    08-14-2012
    Location
    Sydney
    MS-Off Ver
    Microsoft 365
    Posts
    90

    Re: Conditional Formatting with nil value

    Thanks Kaper, I think I understand. But I used Kersplash's option as it worked. I appreciate your ideas. Many thanks.

+ 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. Replies: 1
    Last Post: 12-08-2016, 03:14 PM
  3. Conditional Formatting Removing Previous Conditional Formatting?
    By CravingGod in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2016, 01:02 PM
  4. Replies: 6
    Last Post: 01-08-2016, 06:44 PM
  5. 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
  6. Replies: 1
    Last Post: 09-20-2013, 06:23 PM
  7. Replies: 3
    Last Post: 05-15-2012, 04:13 PM

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