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:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
I want to highlight cells containing formulas

Does that mean you are only looking at cells that contain formulas, not entries?

if so, formulas and functions (generally) look at what is displayed in the cell, not what it contains - ie it would not be able to "see" if a cell contained data or formula
 
Upvote 0
Sorry probably wasn't clear. I want to set conditional formatting on columns J and I where the result of formulas in column J is not equal to zero. Every cell in in column J contains a vlookup formula.

I thought a lot of functions looked at the content of the cells and not the result. For example if I set the condition as J11<>0 it won't work. I've tested the IF function by keying into a test cell on the actual spreadsheet =if(j11<>0,"yes","no") and the returned result is "no". This suggests to me that the IF function looks at cell content ie the formula and not the result.

I've played around with a few other functions eg ISNUMBER and SEARCH but keep on coming up blank, most likely because I'm not formatting the formula correctly.
 
Last edited:
Upvote 0
I thought a lot of functions looked at the content of the cells and not the result.

No, just the other way round. As a test, if A1 contains =10*200 it will display 2000
If you used =LEFT(A1,3) it would return 200 not =10

The only time =J11<>0 would not work, would be if J11 did not contain EXACTLY 0. even 0.000000000001 is <>0

How have you tried to apply the CF so far?
 
Upvote 0
The VLOOKUP formula is referencing an import file from a financial system. Results are in dollars and cents ie numbers with two decimal places. Nil results are always exactly 0, no chance of any .0000000001.

I can't remember everything I've tried - have keyed in dozens of different formulas. I've been googling for solutions and tried combos of suggested solutions ISNUMBER and SEARCH and played around with them - but chances are high I had the formatting incorrect.

Setting the condition as j11*1<>0 didn't work either.
 
Last edited:
Upvote 0
The VLOOKUP formula is referencing an import file from a financial system.
That probably means that the amounts are not really numbers, but text looking like a number. Do a quick test to find out...
=isnumber(cell-ref)
FALSE indicates text and we will need to convert

are high I had the formatting incorrect.
Formatting only changes the visible appearance of a cell - it's cosmetics/how it looks, it does not change the underlying value, and if the cell contents are text anyway, then no formatting will change that to a number
 
Last edited:
Upvote 0
isnumber result is true.

When I said formatting incorrect, I wasn't referring to the format of a cell, I meant the formatting or syntax of the formula I was using as conditional formatting.
 
Upvote 0
OK so it looks like we are dealing with a valid number

Assuming J11 is 1 of the cells you are testing, what does it contain, and what do you get if you just put that formula =J11<>0 into a cell somewhere?
(working blind, so trying to narrow down the options here -bare with me lol)
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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