Conditional formatting confusion when cell is empty

r_ivica

Board Regular
Joined
Jul 13, 2007
Messages
91
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?
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
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.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
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
 

r_ivica

Board Regular
Joined
Jul 13, 2007
Messages
91
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!? :cry:
 

r_ivica

Board Regular
Joined
Jul 13, 2007
Messages
91

ADVERTISEMENT

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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,840
Members
413,944
Latest member
3xc3ln00b

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
Top