Select "No Background" in conditional Formatting

Charlie Artner

I want to clear the background of a specific cell when it is set to a specific value. But when I do that with conditional formatting I get this error message when I select "No Background"

 

 

If I select white as background or any other color, then click OK it works.

 

Any idea if that is a glitch in the system or if that behaviour normal?

Thanks.

 

Jim Hook

Every rule has to have a format specified. However, if you go to the cell/row you are conditionally formatting and set it to the default fill (white for example), it will stay white until the contdition is met and then it will fill with the conditional color. Also, if you want some cell to go from white to yellow to red, for example, set the normal fill to white and then create two conditional format rules, one for red and another for yellow. Whichever rule is satisfied first (going from the top of the rule list down) for a cell is the one that set it. I do that by ordering the yellow rule first followed by the red rule so that the cell goes from white to yellow to red as a limit is approached and then exceeded.

 

Charlie Artner

Hi Jim,

 

thank you for your reply, I am aware that I can change th colors with different rules and first rule that meets criteria will apply, but there are situations when you just want applied rules to be reversed. In my case I want to apply "No Background" to the cell when a certain value is set. But I also have rules that change the color of the row. At that point I don't know what color of the row is currently set, because that depends on another fields value, so I really can't set the "default" background color. Besides, if the background color of the row changes again, this one field stays in the prefious background color.

 

that is the reason why it would be convenient to just remove the background color completely as the original condition of the field was.

 

Also, if this is not an option to be selected in this feature, why even offer it to be selected. Remove this option if it is not part of the possible/allowed selections.

 

But thanks anyway for your time to reply to my questions. Still hoping to get a more satisfying answer or maybe there is a bug in the system.

 

Thanks and God Bless.

Travis

Charlie, rather than selecting "No Background", select the white background option which will get you the same results. 

 

Charlie Artner

Hi Travis,

 

thanks for your response, but it doesn't give me the same result. I have another rule - actually I have a lot of rules - which changes the color of the whole row. So when I select "white" as background, that one cell is white while the rest of the row has a different background.

 

Thanks again for your reply.

J. Craig Williams

Charlie,

 

The conditonal formatting can affect either a cell or an entire row.

Have the "white" first in the list so the other conditional formats won't be applied before it.

 

Craig

Charlie Artner

Craig,

 

thanks for the tip, but unfortunately my conditional formatting is a little more complex than that. I tried that, but then other conditional formating are not correct. Maybe I need to rethink my formatting strategy.

One of my questions still stands, though. Why offer this option if it is not a valid option? I would like to see this option being implemented.

 

Charlie

J. Craig Williams

Charlie,

 

I agree - nice feature request and I'll add my vote to it.

 

Craig

Travis

The "no background" option exists so you can adjust your rules in the future and remove a background if you wanted. I have seen formatting rules with 100+ AND conditions in a single rule (seriously). If they decided they wanted to remove the background color, font color, or Gantt/calendar color, they could do so with the "no background" and "automatic" buttons, rather than having to delete the rule and create another one.