Formula to use with Conditional Formatting based on values in another two cells

[Detectiveclem]

Help needed to achieve the following outcome.

I want cell N6 to change background fill colour depending on the following criteria.

• If Cell K6 is BLANK, I want Cell N6 to have no colour fill.
• If Cell K6 has a date entered I want Cell N6 fill colour to turn red, UNLESS Cell BG6 has any text in it then I want Cell N6 fill colour to change to green.

I only require help with the correct formula to use, as I am comfortable with other aspects of Conditional Formatting.

Any assistance greatly appreciated.

 

[etaf]

NOTE , I have used G6 in place of BG6 - just to simplify the display here in XL2BB and not make it large with a lot of empty space

create 2 rules
select N6

green fill
=And( k6<>"", bg6<>"")

new rule for red fill
=And( k6<>"", bg6="")

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>

N6


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=And( k6<>"", bg6<>"")


Format [Number, Font, Border, Fill] - choose GREEN
choose the format you would like to apply when the condition is true
OK >> OK
REPEAT for RED Fill rule
=And( k6<>"", bg6="")

Book6
	F	G	H	I	J	K	L	M	N	O
4
5		BG6				K6			N6
6		1				1
7
8
9
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N6	Expression	=AND(K6<>"",G6<>"")	text	NO
N6	Expression	=AND(K6<>"",G6="")	text	NO

 

[Detectiveclem]

NOTE , I have used G6 in place of BG6 - just to simplify the display here in XL2BB and not make it large with a lot of empty space

create 2 rules
select N6

green fill
=And( k6<>"", bg6<>"")

new rule for red fill
=And( k6<>"", bg6="")

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>

N6


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=And( k6<>"", bg6<>"")


Format [Number, Font, Border, Fill] - choose GREEN
choose the format you would like to apply when the condition is true
OK >> OK
REPEAT for RED Fill rule
=And( k6<>"", bg6="")

Book6
	F	G	H	I	J	K	L	M	N	O
4
5		BG6				K6			N6
6		1				1
7
8
9
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N6	Expression	=AND(K6<>"",G6<>"")	text	NO
N6	Expression	=AND(K6<>"",G6="")	text	NO

Thank you so much ETAF, I been trying so many options but just couldn't get it to work. Thank it works perfectly now.

 

[etaf]

you are welcome, glad to have helped