Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Format over Conditional Format

    Hi All,

    I have long wondered about what I think is a quirk in Excel (2003). Since I just ran into it again, I thought I'd ask about it.

    Certain sheets in my grade book have cells with 2 conditional formats for shading. There is also a data validation formula on them. If I click to add a "regular" shading to these cells (which are currently blank and no conditional formating shading is present), nothing happens. If I remove both of the conditional formats, the shading I chose, previously unviewable, now appears.

    Another sheet has cells with 1 conditional format for shading. These also have a data validation on them with a list that refers to a named range. If I click to add a "regular" shading to these cells, the shading appears. Again, there is no entry in the cell to invoke the conditional formating.

    While I understand the logic of why a "regular" shading does not appear where the conditional shading has been invoked from the conditions, I don't understand what is happening when the cells are blank.

    Any thoughts?

    Thanks.

    Fred

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You don't mention what the conditions are or the formatting, but I presume that one of the conditions results in no formatting and I also presume that the blank cell matches that condtion. This results in the blank cell taking a conditional formatting which takes precedence over the explicit formatting.

    If you don't believe that to be the case, could you post an example file so we can investigate?

    Steve

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    blank cell satisfying conditional formatting

    Hi Steve,

    I believe you're right.

    On the sheet that I said had 2 CF's, that was a mistake - there are actually 3. The first one examines the length of the entry. If it's 0, there is no formatting and the cell is just left alone, but that's enough to count as a satisfied CF. The 2nd CF looks to see if the value of the entry is 0 and colors it rose (to signify a missed quiz), The 3rd CF looks to see if the value of the entry is 0.000001 and colors it pink (to signify the person handed in a blank quiz). So when I try to explicitly format that cell, nothing happens. But if I put in a grade of, say, 89, then I can explicitly format the cell. I think I did it this way bcs blank cells (before a quiz was given) were being treated as 0 and the CF shading was being applied.

    The sheet that only had 1 CF does not have a test on the entry's length. This is an attendance sheet where the CF looks for "ab" (for absent) and colors it rose if that's the value in the cell.

    I think this solves it. If I had remembered that 1st CF on the quiz sheet, that would have resolved it. For some reason, when I looked at the CF's, it may not have dawned on me that "no CF formatting" due to a satisfied condition was overriding the explicit formatting. But as soon as I entered a "real" grade >0, none of the CF's were satisfied so I could now use explicit formatting successfully.

    Thanks again.

    Fred

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I still think that there is an "error" in your reasoning is thinking it had "no CF Formatting". It must have CF formatting. The formatting is propably to remove the shading, thus overriding the explicit shading you tried to add. If the CF does not conditional change a format, the explicit one is used, even if the condition is satisfied.

    Steve

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Right again

    Hi Steve,

    I checked again - carefully this time. I believe the first condition, the one that examines the length to see if it's 0, does have a format - it shades the cell white. So clearly the CF is satisfied and the shading is applied. So only if the cell has a "valid" quiz grade like 87 are all of the CF's unsatisfied - hence the ability to explicitly shade.

    And I tried a little experiment - set a CF on a cell but did not set any formats. Now I could add explicit shading regardless of whether the CF is set. But it looks like each type of CF formatting is exclusive of the others. If I set a shading in the CF and the condition is satisfied, I can not explicitly shade the cell. But I can still put a border around it even if the condition is satisfied. If I include border in the CF formatting, then I can no longer set an explicit border if the condition is satisfied.

    Fred

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    But it looks like each type of CF formatting is exclusive of the others.
    Yes this is true. The conditions are not "additive". You can not have a CF like red text for >5 and yellow background for >10 and expect that >10 will be red text AND a yellow background. It will be one or the other. The one chosen is the first condition met, so order of the CFs is important. If the yellow bkgrd >10 is CF1 and CF2 is red text. numbers 6,7,8,9 will be red, but 11, 12 will be yellow. If CF1 is red text for >5 and CF2 is Yellow Bkgrd for >10 then an 11 will be red text, no yw background. In fact nothing will be colored by CF2 since CF1 is met before it tests for CF2...

    Steve

Tags for this Thread

Posting Permissions

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