Conditional Formatting Colours when cell is blank.

Question

I just want a simple conditional format for the following:

Fill green if cell is >or = to 70%

Fill red if cell is < 70%

Fill white if cell is blank.

I've tried to rearrange the conditions in all different ways, however I always get a red fill if the cell is blank.

Thank you for any help you can provide.

Answer 1

Hi Biff,

 

No I am not.

Sorry, maybe you could explain your second suggestion to me in more detail.

I'm new at this CF.

Thanks for your time.

Try this...

Assume the cell of interest is cell A1.

• Select cell A1
• Goto the menu Format>Conditional Formatting
• Select the Formula Is option
• Enter this formula in the box on the right:

=AND(COUNT(A1),A1>=0.7)

• Click the Format button
• Select the Patterns tab
• Select a shade of GREEN
• OK
• Click the Add button
• Select the Formula Is option
• Enter this formula in the box on the right:

=AND(COUNT(A1),A1<0.7)

• Click the Format button
• Select the Patterns tab
• Select a shade of RED
• OK you way out

--

Biff

Microsoft Excel MVP

Answer 2

Hi Biff,

No I am not.

Sorry, maybe you could explain your second suggestion to me in more detail.

I'm new at this CF.

Thanks for your time.

Answer 3

I just want a simple conditional format for the following:

Fill green if cell is >or = to 70%

Fill red if cell is < 70%

Fill white if cell is blank.

I've tried to rearrange the conditions in all different ways, however I always get a red fill if the cell is blank.

Thank you for any help you can provide.

The default fill color is white (technically classified as: no fill).

Are you using some other default fill color other than white?

--

Biff

Microsoft Excel MVP

Answer 4

Hello Rick,

Sorry I don't quite understand.

I added a third condition that is:  Cell value is equal to  =""  and this doesn't work.

I'm sorry that I don't understand your second suggestion. 

Thanks

Answer 5

You could always add a third condition for the cell equalling "" and set that condiition's color to "No Color". There is also the possibility of using a Conditional Formatting formula and use and AND function call specifying the cell <>"" and couple that with your current conditional.

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.