Welcome guest. Before posting on our computer help forum, you must register. Click here it's easy and free.

Author Topic: Excel - Conditional formating is displaying cell contents  (Read 2238 times)

0 Members and 1 Guest are viewing this topic.

tigeron

    Topic Starter


    Starter

    • Experience: Experienced
    • OS: Windows 7
    Excel - Conditional formating is displaying cell contents
    « on: September 19, 2018, 09:11:11 PM »
    First thing is, I am still using Excel 97.  Have had no reason to upgrade.
    The following formula is in cell H9 and works fine.  If cell F7 is "", nothing shows up in cell H9.
    Which is exactly what I want.  If cell F7<>"", then a result is displayed in cell H9.

    =IF(F7<>"",(SUM(H7:H8)+D12),D12)

    The problem is when I apply conditional formatting to cell H9 to change the cell color, cell
    H9 displays a result even when cell F7="".

    Questions:
    1.  Why is nothing displayed in cell H9 when just using the above formula?
    This is not a problem because I don't want anything to be displayed until
    cell F7 has something entered into it.

    2.  Why after applying conditional formatting a result in cell H9 is displayed, even when
    cell F7=""?

    What can I do so cell H9 does not display a result after applying conditional formatting?
    That is until cell F7 contains a entry.

    I was thinking that using a Visual Basic macro to scan the sheet every time the Enter key
    was pressed, might do the trick.  But I don't want to use a macro unless I have to.

    Any help would be greatly appreciated.

    tigeron

      Topic Starter


      Starter

      • Experience: Experienced
      • OS: Windows 7
      Re: Excel - Conditional formating is displaying cell contents
      « Reply #1 on: September 20, 2018, 08:47:43 AM »
      I guess that I should add that the cell H9 color is changed based upon its own value.
      color "orange" if the sum is between 1 and 50.
      color "yellow" if the sum is <1.

      Cell H9 has a initial background color but I don't think that matters concerning the problem.

      In reading some way back posts, it seems if I use Formula instead of Cell Value in the conditional
      formatting, that it might work.  That is that cell H9 does not display anything until F7 has a entry,
      either text or a number.

      tigeron

        Topic Starter


        Starter

        • Experience: Experienced
        • OS: Windows 7
        Re: Excel - Conditional formating is displaying cell contents
        « Reply #2 on: September 20, 2018, 09:18:51 AM »
        I just tried using Formula in conditional formatting with the same result.
        Once the formatting is applied, cell contents in H9 are displayed even
        with F7 being a open string.  So I am still at a loss.  Can anyone help?

        BC_Programmer


          Mastermind
        • Typing is no substitute for thinking.
        • Thanked: 1140
          • Yes
          • Yes
          • BC-Programming.com
        • Certifications: List
        • Computer: Specs
        • Experience: Beginner
        • OS: Windows 11
        Re: Excel - Conditional formating is displaying cell contents
        « Reply #3 on: September 20, 2018, 11:27:40 AM »
        oldest I can go at the moment is Excel 2000, I created a test spreadsheet

        1. Put formula in both H9 and I9
        2. placed values in H7 and H8 (19 and 12 respectively)
        3. Applied conditional formatting to H9.

        (I've also left D12 blank).

        H9 and I9 both display the same value, with H9 having colour formatting. With a value in F7 they both show "31" with H9 being coloured orange. If I delete the contents of F7, they both display 0 and H9 is coloured Yellow. I couldn't notice any difference between when conditional formatting was on the cell and when it wasn't.

        You've described the formula intent as showing blank if there is no value in F7, however, The way the IF is structured, then if F7 has no entry, then H9 will have the value in D12.

        Further, even if D12 is blank itself, the value will be converted to a number because the other IF Expression has a Numeric result. If you want it to be blank you can instead indicate that directly in the expression, instead of the value in D12:

        Code: [Select]
        =IF(F7<>"",(SUM(H7:H8)+D12),"")
        I was trying to dereference Null Pointers before it was cool.

        tigeron

          Topic Starter


          Starter

          • Experience: Experienced
          • OS: Windows 7
          Re: Excel - Conditional formating is displaying cell contents
          « Reply #4 on: September 21, 2018, 01:06:17 PM »
          I added the  "" at the end of the formula and that took care of the problem.  But it also created another problem in another cells with a (VALUE!) error.  I worked for many hours to fix the problem resulting in my formulas getting longer.

          But I was now thinking outside the box from a new perspective.  The solution is so simple that neither of us saw it.
          I did a conditional format and let the cell contents be displayed anyway when I wanted no display at all.  The solution
          was to add another conditional format that would change the font color to the cell fill color when cell F7="".

          The cell was still displaying the contents but with the font being the same color as the fill, you could not see what was being displayed.  So I went back and used my original formulas because they were more simple and they worked.  It also saved me the hassle of having to change many formulas on many other sheets.

          I also talked to a friend for over an hour and he told me that using Windows 7, which I am using, has a good possibility of making Excel 97 do funny things.  So I fired up an XP computer and got the same display result with conditional formatting.  That is when I started to think outside of the box.

          Many thanks for your help BC_PROGRAMMER.  you pointed me in another direction.