Conditional formatting of cells based on formula result not equalling zero

elemenopee

New Member
Joined
Mar 16, 2017
Messages
16
I want to highlight cells containing formulas based on the result of the formula not equalling zero.

Two columns
Column IColumn J
Account Name 1$0.00
Account Name 2$20.00
Account Name 3$0.00
Account Name 4($10.00)
<colgroup><col width="249" style="width: 187pt; mso-width-source: userset; mso-width-alt: 9106;"> <col width="272" style="width: 204pt; mso-width-source: userset; mso-width-alt: 9947;"> <tbody> </tbody>


Column J contains a VLookup formula.

I know I need to use a formula in conditional formatting, but can't get it working.

Any suggestions?
 
Last edited:
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.
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top