Formula for Conditional formating

[Nixson]

what is the formula, if the Current location Colum is Dubai then (No of Days) coloum should show green colour, if Other than Dubai Location and No of days is Morethan - 30 Days it need to show red colour & Less than - 30 should show yellow colour, what is formula for that,

Dewars Status	16/03/2023
Serial No.	WC	Current location	Despatch Date/Received Date	No. of Days
4936	100LTR	DUBAI	5-Oct-22	-162
4937	100LTR	GHANA	13-Mar-23	-3
4938	100LTR	INDIA	8-Dec-21	-463
4939	100LTR	CHINA	11-Dec-21	-460
7557	100LTR	DUBAI	17-Feb-22	-392
7559	100LTR	DUBAI	8-Dec-21	-463
7561	100LTR	DUBAI	19-Dec-22	-87
7562	100LTR	DUBAI	29-Apr-22	-321
7563	100LTR	DUBAI	5-Oct-22	-162
7564	100LTR	DUBAI	11-Dec-21	-460
6219	100LTR	DUBAI	15-Aug-21	-578
7558	100LTR	DUBAI	5-Aug-21	-588

 

[etaf]

just to clarify

Rules

assuming A,B,C etc columns

confused
Green = column C = Dubai
But what in the days column ?

=AND( $C2 = "dubai", $E2 = ????

Or is that just if Dubai
then
=$C2 = "dubai"

RED
=AND( $C2 <> "dubai", $E2 > 30)

Yellow
=AND( $C2 <> "dubai", $E2 < 30)

what colour if actual 30 days

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

Highlight applicable range >>
A2:E100 - Change, reduce or extend the rows to meet your data range of rows

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( $C2 <> "dubai", $E2 < 30)

Format [Number, Font, Border, Fill] YELLOW
choose the format you would like to apply when the condition is true
OK >> OK

repeat for other rules


in your example no days > then and not dubai

Book3
	A	B	C	D	E
1	Dewars Status	3/16/23
2
3	Serial No.	WC	Current location	Despatch Date/Received Date	No. of Days
4
5	4936	100LTR	DUBAI	5-Oct-22	-162
6	4937	100LTR	GHANA	13-Mar-23	-3
7	4938	100LTR	INDIA	8-Dec-21	-463
8	4939	100LTR	CHINA	11-Dec-21	-460
9	7557	100LTR	DUBAI	17-Feb-22	-392
10	7559	100LTR	DUBAI	8-Dec-21	-463
11	7561	100LTR	DUBAI	19-Dec-22	-87
12	7562	100LTR	DUBAI	29-Apr-22	-321
13	7563	100LTR	DUBAI	5-Oct-22	-162
14	7564	100LTR	DUBAI	11-Dec-21	-460
15	6219	100LTR	DUBAI	15-Aug-21	-578
16	7558	100LTR	DUBAI	5-Aug-21	-588
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:E16	Expression	=AND($C5<>"dubai", $E5<30)	text	NO
E5:E16	Expression	=AND( $C5 <> "dubai", $E5 > 30)	text	NO
E5:E16	Expression	=$C5="dubai"	text	NO

 

[Sufiyan97]

See if this works

Book1
	A	B	C	D	E	F	G
1	Dewars Status	16/03/2023
2
3	Serial No.	WC	Current location	Despatch Date/Received Date	No. of Days
4
5	4936	100LTR	DUBAI	10/5/2022	-162
6	4937	100LTR	GHANA	3/13/2023	-3
7	4938	100LTR	INDIA	12/8/2021	-463
8	4939	100LTR	CHINA	12/11/2021	-460
9	7557	100LTR	DUBAI	2/17/2022	-392
10	7559	100LTR	DUBAI	12/8/2021	-463
11	7561	100LTR	DUBAI	12/19/2022	-87
12	7562	100LTR	DUBAI	4/29/2022	-321
13	7563	100LTR	DUBAI	10/5/2022	-162
14	7564	100LTR	DUBAI	12/11/2021	-460
15	6219	100LTR	DUBAI	8/15/2021	-578
16	7558	100LTR	DUBAI	8/5/2021	-588
17
Sheet7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:E16	Expression	=AND(C5<>"Dubai",E5<-30)	text	NO
E5:E16	Expression	=AND(C5<>"Dubai",E5>-30)	text	NO
E5:E16	Expression	=C5="Dubai"	text	NO