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?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
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,836
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,156
Messages
5,835,707
Members
430,381
Latest member
tntcute

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