Conditional formatting : Zeroes and blanks

After years of using Excel I have discovered today that Excel considers a numeric zero to be the same as an empty cell (at least in conditional formats).

The background to this, in case it helps, is that I have a series of expected results from a test run (in column E) and these are compared to a set of actual results loaded in from a SQL Server database (in column H).

My conditional format compares two cells using a formula like this ...

Please Login or Register  to view this content.

H50 and E50 are the cells being compared.
$B$2 is a flag to determine whether or not SHOW the difference (using a pattern of red)
The "*" test is there to allow certain values to be ignored as they are known to always be different, timestamps for example.

What I am seeing is a numeric zero in E50 being compared to a NULL (empty cell) in H50 and being considered (by Excel) as identical.

I COULD alter the underlying data to make all cells text but this would be difficult to achieve across the project (hundreds of workbooks).

Can anyone help with a method of getting this compare to work "properly"?

Thanks in advance.

Try instead:

Please Login or Register  to view this content.

Note: You don't actually need to create an IF() statement in conditional formatting....it's an IF by default...just need to include the IF_TRUE part....

Thankyou both for your responses.

NBVC - yours was fine (thanks) except it didn't allow for both cells being empty, it did, however, lead me to something that DID work 100%. many thanks.
My solution (in case anybody else wants it later) was ...

Please Login or Register  to view this content.

DaddyLongLegs, then I read yours which is a much neater and more complete solution than mine, many thanks.

Thanks both also for the lesson on conditional formatting not needing the IF, I've used it for years and years without knowing that.