Use Conditional Formatting When Reference Cell Not Blank

**damalco** · 07-08-2010, 12:56 PM

I am using conditional formatting to change the fill color of a cell if the value does not equal a reference cell. The problem I am having is that the reference cell will be blank after data is entered into the cell with the conditional formatting. Obviously, when the data is entered the two cells are no longer equal and it changes color based on the condition being true (blank<>data entered). How can I keep it from changing color if the reference cell is blank or only allow it to change color if the reference cell has data entered in it?

**Cheeky Charlie** · 07-08-2010, 01:04 PM

=or(<current criterion>,<is blank>)

hth

**damalco** · 07-08-2010, 01:30 PM

Originally Posted by Cheeky Charlie

=or(<current criterion>,<is blank>)

hth

I tried your suggestion in the conditional formatting dialog box in various forms and it did not work. I'm not sure I understand where and how exactly I should enter that condition.

Written in code format, I am trying to do the following:

IF reference cell=blank OR reference cell=this cell THEN no color change; IF reference cell<>blank AND reference cell<>this cell THEN change color.

Sorry, I'm an idiot and may not have explained it well enough originally.

**Cheeky Charlie** · 07-09-2010, 06:17 AM

This:
IF reference cell=blank OR reference cell=this cell THEN no color change; IF reference cell<>blank AND reference cell<>this cell THEN change color.
Appears to be a dual statement - I take it you only want one condition to fire or not?
On that basis:
cell with formatting = A1
cell to match = B1
conditional format condition:
=and(A1<>B1,B1<>"")

hth

**damalco** · 07-09-2010, 10:17 AM

Originally Posted by Cheeky Charlie

This:
IF reference cell=blank OR reference cell=this cell THEN no color change; IF reference cell<>blank AND reference cell<>this cell THEN change color.
Appears to be a dual statement - I take it you only want one condition to fire or not?
On that basis:
cell with formatting = A1
cell to match = B1
conditional format condition:
=and(A1<>B1,B1<>"")

hth

I finally figured out how to make it work from your original post. I was trying to use the "Cell Value Is" rather than the "Formula Is" option in the conditional formatting dialog box. However, it does not recognize a space surrounded by quotes (" ") as "blank." I had to use the condition that the cell is not equal to zero (B1<>0) instead.

Thanks for your help, it got me down the right path.

**Cheeky Charlie** · 07-09-2010, 02:09 PM

No probs, if you don't include the space between the blanks that will work too - in some situations the difference is significant, but probably not here.