Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Mystery in conditional formatting

    Excel 2003
    I have a Conditional Format set which colours cells according to their contents:
    Base colour of the cell is GREY
    If the cell = 0 YELLOW
    If the cell = -1 ORANGE
    If the cell is = -2 RED
    So the cells are all GREY unless they contain one of the designated numerical values.

    All seemed to be working perfectly, until some (expected) changes occurred, and here's the problem:

    A cell which has always been empty is indeed GREY, but a cell which once contained a numerical value that is subsequently deleted appears to Conditional Formatting to have the value 0 and remains YELLOW.

    For all other intents and purposes this errant cell is blank - it tests as "", but Conditional Formatting has a different view !

    If I copy the VALUE of an "always-has-been-blank" cell into one where CF thinks it contains a zero but it doesn't, it goes GREY - as it should.

    Clearly something is different in cells which have always been blank as opposed to ones which are blank but at one time have contained a number.

    I can think of several very clumsy workarounds, but am I missing an obvious way to test for there being a 0 in a cell that doesn't mislead me when the cell is blank ?

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Well, I've succeeded with a simple workaround:

    I've made the base colour YELLOW and the three tests are:
    For a blank cell: GREY
    For -1: ORANGE
    For -2: RED

    I'm still mystified how there can be two different versions of an apparently emtpy cell, one of which CF thinks is zero, the other not !
    Last edited by MartinM; 2011-07-21 at 13:02.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Could you post a sample showing the issue? And just confirm that you cleared the cell with the delete key?
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Sample is attached and yes, I was using the delete key.

    To add to the mystery, if you "delete" the contents of one of the blank (GREY) cells using the delete key, CF immediately thinks it has become zero.
    Attached Files Attached Files
    Last edited by MartinM; 2011-07-21 at 18:23.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Your grey cells are not empty - they have formulas in them. A truly blank cell will pass an =0 check (test that on any cell you haven't used) and hence will meet your CF criterion - you need a check like =AND(B2=0,B2<>"") to ignore blank cells.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •