+ Reply to Thread
Results 1 to 12 of 12

Color formatting issues - I want zero formatting in cell containing text?

  1. #1
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Color formatting issues - I want zero formatting in cell containing text?

    Hi all,

    I have made a dummy file/sheet of larger sheet I've set up. Basically, I have a sheet with 'raw data' which is updated from another source with copy and paste. Linked to this sheet is another sheet which pulls data directly from the sheet with raw data.

    Often, the raw data sheet might have empty cells.

    Referencing an empty cell returns a zero in Excel in my linked sheet. This is not desirable. So, I have worked around this by using an IFERROR formula. The result is a blank cell also in the linked sheet. Nice.

    The problem is however that my color formatting seems to include the blank cells also. I think my blank cell basically contains text. I've tried using various formula, but I can't make it to work. This is using the conditional formatting tools built in Excel.

    Any advice?

    Formatting.png
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Color formatting issues - I want zero formatting in cell containing text?

    change condition to =AND(NOT(ISBLANK(E7)),E7<0) and =AND(NOT(ISBLANK(E7)),E7>0)

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Color formatting issues - I want zero formatting in cell containing text?

    That will not work. ISBLANK only works with empty cells, not formula blanks.

    For negatives use =E7<0

    For positives use =N(E7)>0

    What about genuine zero values in the raw data as opposed to empty cells?

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Color formatting issues - I want zero formatting in cell containing text?

    How about
    =AND(E7>0,E7<>"")
    =AND(E7<0,E7<>"")

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Color formatting issues - I want zero formatting in cell containing text?

    Another.

    =AND(ISNUMBER(E7),E7<0)
    =AND(ISNUMBER(E7),E7>0)
    Dave

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Color formatting issues - I want zero formatting in cell containing text?

    I should have thought of this sooner,

    First condition negative numbers, stop if true must be checked.

    =E7<0

    Second condition positive numbers

    =E7

    No blank checks needed.

  7. #7
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Color formatting issues - I want zero formatting in cell containing text?

    Excellent! Thank you so much for all contributions. I added a reputation to all of you, but went with Fluff13's solution. Very elegant and efficient.

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Color formatting issues - I want zero formatting in cell containing text?

    There is no right or wrong way to do what you need, any of the suggestions will do it.

    In terms of efficiency, my first suggestion in post #3 uses 3 calculations per cell. Fluff and Flame's suggestions both use 4 calcs per cell, my follow up suggestion when something obvious hit me right between the eyes only uses 1 calc per cell for negatives or 2 for anything else.

    It might look to simple to work but it does

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Color formatting issues - I want zero formatting in cell containing text?

    Glad we could help & thanks for the feedback

  10. #10
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Color formatting issues - I want zero formatting in cell containing text?

    Quote Originally Posted by jason.b75 View Post
    There is no right or wrong way to do what you need, any of the suggestions will do it.

    In terms of efficiency, my first suggestion in post #3 uses 3 calculations per cell. Fluff and Flame's suggestions both use 4 calcs per cell, my follow up suggestion when something obvious hit me right between the eyes only uses 1 calc per cell for negatives or 2 for anything else.

    It might look to simple to work but it does

    Hi Jason,

    Your post about efficiency caught my attention. I have trouble with a large sheet of mine crashing due to conditional formatting, so I'd like to try your formula.

    However, I can't make it work?

    It works for negatives, but using the simple =E7 for positives, I get green cells on the entire column...

    Can you help?

    Alternatively add the formula in the sheet I uploaded and reupload it in case there are some formula differences between Norway and England? I know there can be some separators that can be different.

    Thanks in advance!

    Elijah

  11. #11
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Color formatting issues - I want zero formatting in cell containing text?

    The formula in this case would be the same for both versions, there are no separators or functions to translate incorrectly.

    2 things to check, first that the 'Stop if True' box is checked for the negatives. If it is not, the negatives will be formatted as positives.

    Second that the rule is looking at the correct cell, when you apply the rule, the formula should refer to the active cell, it is best if the top left cell of the range to which the rule is being applied is active, otherwise things can go wrong quite easily.

  12. #12
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Color formatting issues - I want zero formatting in cell containing text?

    Quote Originally Posted by jason.b75 View Post

    2 things to check, first that the 'Stop if True' box is checked for the negatives. If it is not, the negatives will be formatted as positives.
    This seems to have been the error.

    Worked out fine now. Thanks a lot, man!

+ 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. Swapping cell content to INCLUDE CELL FORMATTING (Text color, background color, etc)
    By jcpeterson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-10-2014, 10:09 AM
  2. Replies: 10
    Last Post: 06-19-2013, 05:28 PM
  3. Conditional formatting to color text cell that represent data cells
    By dorkichar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-10-2013, 06:38 PM
  4. [SOLVED] Conditional Formatting (changing color) in one cell based on text in two cells
    By Chadyoung1 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-14-2013, 04:26 AM
  5. [SOLVED] Excel 2007 : Conditional formatting cell color based on text
    By herukuncahyono in forum Excel General
    Replies: 4
    Last Post: 07-19-2012, 02:59 AM
  6. Formatting Cell Color to Specific Text
    By andreabeas in forum Excel General
    Replies: 9
    Last Post: 03-25-2009, 03:54 PM
  7. Replies: 3
    Last Post: 08-16-2006, 09:00 AM

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