Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Conditional formatting of cells based on formula result not equalling zero

This is a discussion on Conditional formatting of cells based on formula result not equalling zero within the Excel Questions forums, part of the Question Forums category; Originally Posted by FDibbins OK so J11 contains 0 And you want to test for cells NOT containing zero, correct? ...

  1. #11
    New Member
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    Quote Originally Posted by FDibbins View Post
    OK so J11 contains 0

    And you want to test for cells NOT containing zero, correct?

    If so...
    1. highlight the range you want to apply the conditional formatting to
    2. on the home tab, styles, select CF
    3. select new rule, select use formula
    4. enter =$J11<>0 format fill as needed

    edit: what cell does your table start in? Adjust the J11 to match the 1st row you are testing
    I've already tried that - it doesn't work.

  2. #12
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    5,440

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    If you tried that and it didn't work, 1 of 2 things are happening...
    1. your numbers are text
    2. numbers that you think are not zero, really are zero

    Did you see my edit above?

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  3. #13
    New Member
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    Quote Originally Posted by FDibbins View Post
    If you tried that and it didn't work, 1 of 2 things are happening...
    1. your numbers are text
    2. numbers that you think are not zero, really are zero

    Did you see my edit above?
    Have seen your edit. Table starts at row 11.

    I suspect excel looks at the contents of a cell (ie the formula) and not what is displayed (ie the result of the formula) when using the IF statement in conditional formatting. I have the same issue with using the SEARCH function to search for specific numbers when the cells I've highlighted contain formulas.

    Thanks so much for all of your help with this. I'm going to rethink what I'm trying to achieve here and tackle it from a different angle.
    Last edited by elemenopee; Mar 20th, 2017 at 09:07 PM.

  4. #14
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    5,440

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    I suspect excel looks at the contents of a cell (ie the formula) and not what is displayed (ie the result of the formula) when using the IF statement in conditional formatting.
    No, it doesn't, as I have already explained.

    Lets start with some basics here. The thread is now over 2 pages (for me), so to keep things current, with J11 as the starting point...
    1. You want to test for - and color - any cells (J11) that show anything NOT equal to zero
    2. You have already confirmed that J11 contains a real number, and not text looking like a number by using =isnumber(J11), and that came back TRUE
    3. When you enter in a cell somewhere =J11<>0 you get TRUE
    4. When you use Conditional Formatting/Use Formula =J11<>0 and format fill RED, it does not turn red

    You did not yet tell me exactly what is in J11? Not just what it shows, but also what it contains. (please reply to this 1)

    If you answered yes to ALL of those, especially 3 and 4, then I have got to the point where I will need to see a sample workbook. you will need to upload it to dropbox, but I am at work at the moment and cannot access that site from here. I will be at home in a few hours and can take a look at the file then

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  5. #15
    New Member
    Join Date
    Mar 2017
    Location
    Australia
    Posts
    14

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    Quote Originally Posted by FDibbins View Post
    No, it doesn't, as I have already explained.

    Lets start with some basics here. The thread is now over 2 pages (for me), so to keep things current, with J11 as the starting point...
    1. You want to test for - and color - any cells (J11) that show anything NOT equal to zero
    2. You have already confirmed that J11 contains a real number, and not text looking like a number by using =isnumber(J11), and that came back TRUE
    3. When you enter in a cell somewhere =J11<>0 you get TRUE
    4. When you use Conditional Formatting/Use Formula =J11<>0 and format fill RED, it does not turn red

    You did not yet tell me exactly what is in J11? Not just what it shows, but also what it contains. (please reply to this 1)

    If you answered yes to ALL of those, especially 3 and 4, then I have got to the point where I will need to see a sample workbook. you will need to upload it to dropbox, but I am at work at the moment and cannot access that site from here. I will be at home in a few hours and can take a look at the file then
    Thanks for all your help yesterday. I've actually just now worked out the solution and it was so easy in the end. I noticed in the menu options under conditional formatting there were options for cell rules with greater than a number and less than a number. I created two rules using the menu options, one for highlighting cells greater than zero and one for highlighting cells less than zero. When I looked at the formulas under Manage Rules, the rules automatically created are "Cell Value > 0" and "Cell Value < 0". Anyway, thanks for all of you help yesterday, I really appreciate it.
    Last edited by elemenopee; Mar 21st, 2017 at 04:20 PM.

  6. #16
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    5,440

    Default Re: Conditional formatting of cells based on formula result not equalling zero

    Im happy you got this resolved, thanks for letting me know

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

Page 2 of 2 FirstFirst 12

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com