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?
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? ...
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
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.
No, it doesn't, as I have already explained.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.
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
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.
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
Like this thread? Share it with others