# Conditional formatting confusion when cell is empty

#### r_ivica

##### Board Regular
i have this conditional format:

Condition 1:
Formula =AND(\$R\$5>=\$H\$5;\$H\$5<>0)
RED COLOR
Condition 2:
Value of cell is smaller than =\$H\$5
GREEN COLOR

So this works ok, but problem is when in R5 is empty cell he gives me red color but i want when cell is empty - no to have that cell colored - i want to be white as default!

How to solve this?

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Condition 1 Formula Is:
=((\$R\$5>=OFFSET(\$R\$5,0,-10))*(\$H\$5<>0)*(NOT(ISBLANK(\$R\$5))))

Condition 2 Formula Is:
Change the >= to <

The use of multiplication relies on the fact that "false" is numerically 0, so if anything is false the whole value becomes 0 (anything times zero is zero), which means the whole statement is zero, so "false".

I recommend using the Offset formula in conditionally formatting so that inserting rows doesn't give you reference errors in the formula - everything depends only on the one cell the formula is in (in this case, it looks ten rows to the left, rather than at column H per se).

It may be easier to view these formulas in a regular cell if you want to take them apart.

BTW always watch out for the effect of empty cells on formula. So far as my tests go, a blank cell is greater than 0 and also less than 0 -- you can imagine how this throws conditional testing out the window. Regards

not sure if i understand this formulas but i inserted it right as you told me here, and still have red color when cell is empty!?

but now i see what is potential problem. This cell R5 is linked to another cell where is this formula:

=IF(Q104="";"";MROUND((Q104);CHOOSE(MATCH((Q104);{1,01;2;3;4;6;10;20;30;50;100});0,01;0,02;0,05;0,1;0,2;0,5;1;5;10)))

so in R5 is actually =R104 where is this formula above.

So when i delete this cell with Delete on my keyboard it really gives me white format as i want because that cell IS empty as excel see, but when have this inside =R104 which is linked to those R104 cell where is empty cell - according to formula provided above where is "" condition - excel don't format right as empty!

any new ideas how to solve this? it would help me lot!

Try

=AND(\$R\$5>=\$H\$5;\$H\$5<>0;\$R\$5<>"")

Try

=AND(\$R\$5>=\$H\$5;\$H\$5<>0;\$R\$5<>"")

working great! thanks to all for helping :wink:

Replies
4
Views
355
Replies
5
Views
76
Replies
1
Views
234
Replies
7
Views
96
Replies
1
Views
427

1,203,060
Messages
6,053,303
Members
444,650
Latest member
bookendinSA

### 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.

### Which adblocker are you using?

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

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