Consitional Formatting issue

[Cordo]

Hi Guys,

I have conditional formatting that says:

Cell Value is equal to "N/A" Black Colour Fill
Cell Value is Less than or equal to =A1 Green Colour Fill
Cell Value is greater than =A1 Red Colour Fill


Thats all fine but if a cell has nothing in it, I want there to be no fill at all. I assumed it would have no fill as it wouldnt meet any of the criteria in the 3 conditional formats.

Any thoughts?

 

[charleymax]

Hi Guys,

I have conditional formatting that says:

Cell Value is equal to "N/A" Black Colour Fill
Cell Value is Less than or equal to =A1 Green Colour Fill
Cell Value is greater than =A1 Red Colour Fill


Thats all fine but if a cell has nothing in it, I want there to be no fill at all. I assumed it would have no fill as it wouldnt meet any of the criteria in the 3 conditional formats.

Any thoughts?

Cell Value is Less than or equal to =A1 Green Colour Fill

Your condition has to meet this one i.e the cell to colour is empty and the criteria cell is empty therefore it will colour Green...

I.e. if B1 has conditional format where Value is Less than or equal to =A1 Green Colour Fill... B1 will be green if both B1 and A1 are blank

 

[Cordo]

Cell Value is Less than or equal to =A1 Green Colour Fill

Your condition has to meet this one i.e the cell to colour is empty and the criteria cell is empty therefore it will colour Green...

I.e. if B1 has conditional format where Value is Less than or equal to =A1 Green Colour Fill... B1 will be green if both B1 and A1 are blank

Okay, so what would be the change to that condition?

 

[VoG]

For the green condition try using Formula Is and a formula like

=AND(B1<>"",B1< A1)<a1)></a1)>

 

[Cordo]

For the green condition try using Formula Is and a formula like

=AND(B1<>"",B1< A1)<A1)></A1)>

Its strange, at first the cell was red, now that I have put in that formula, it has turned green. I just want the cell to have no fill?

 

[VoG]

Try like this

Excel Workbook
	A	B
1		#N/A
2		1
3	4	1
4		2
5	1	2
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	1. / Formula is =AND(B1>A1,A1<>"")	Abc
B1	2. / Formula is =AND(B1<=A1,A1<>"")	Abc
B1	3. / Formula is =ISNA(B1)	Abc