conditional Formatting or VBA Question

[steve400243]

Hello, I need to get the best way to have the attached update the Status Column from NOT AVAILABLE, To AVAILABLE, and to COMPLETE. Once the 4 Needed Documents( Columns H, I, J, K) are all entered in the Status would change to AVAILABLE. And be Green, Would be red and Not Available until then. After all columns are used in the row Status would change to Complete and be Purple. This could have several Housebills. I have 3 listed for reference. Thanks in advance.

TEST SHEET.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Vessel:	Container:	Master Bill:
2	APL QINGDAO	OOLU123456789	COSU6363636363
3
4	Packet Received:	ETA Date:	Container Released:	Container Received	Devanned On:	Storage 7th Day:	Storage 12th Day:
5	1-Sep-2021	1-Sep-2021	3-Sep-2021	3-Sep-2021	3-Sep-2021	9-Sep-2021	14-Sep-2021
6
7	Housebill:	Status:	Office:	Customer:	Mode:	Cartons:	CBM:	Customs Release:	OBL Notification:	Entry:	DO Received:	Pickup Date:	Released to:	PRO:	Shipper PLTS	WHSE PLTS
8	ABCD123456789	AVAILABLE	SEA	ABC CORP	FMM	15	10.5	4-Sep	4-Sep	799-12345678	4-Sep
9	ABCD123456790	NOT AVAILABLE	SEA	ABC CORP	FMM	15	10.5	4-Sep	4-Sep
10	ABCD123456791	COMPLETE	SEA	ABC CORP	FMM	15	10.5	4-Sep	4-Sep	799-12345678	4-Sep	4-Sep	Truck	12345678-9	5	5
Sheet2

Cell Formulas
Range	Formula
F5	F5	=E5+6
G5	G5	=E5+11

 

[JGordon11]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Vessel:	Container:	Master Bill:
2	APL QINGDAO	OOLU123456789	COSU6363636363
3
4	Packet Received:	ETA Date:	Container Released:	Container Received	Devanned On:	Storage 7th Day:	Storage 12th Day:
5	9/1/2021	9/1/2021	9/3/2021	9/3/2021	9/3/2021	9/9/2021	9/14/2021
6
7	Housebill:	Status:	Office:	Customer:	Mode:	Cartons:	CBM:	Customs Release:	OBL Notification:	Entry:	DO Received:	Pickup Date:	Released to:	PRO:	Shipper PLTS	WHSE PLTS
8	ABCD123456789	AVAILABLE	SEA	ABC CORP	FMM	15	10.5	9/4/2021	9/4/2021	799-12345678	9/4/2021
9	ABCD123456790	NOT AVAILABLE	SEA	ABC CORP	FMM	15	10.5	9/4/2021	9/4/2021
10	ABCD123456791	COMPLETE	SEA	ABC CORP	FMM	15	10.5	9/4/2021	9/4/2021	799-12345678	9/4/2021	9/4/2021	Truck	12345678-9	5	5
Sheet7

Cell Formulas
Range	Formula
F5	F5	=E5+6
G5	G5	=E5+11
B8:B10	B8	=IF(SUMPRODUCT(--(C8:P8<>""))=14,"COMPLETE",IF(SUMPRODUCT(--(H8:K8<>""))=4, "AVAILABLE","NOT AVAILABLE"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8:B10	Expression	=B8="COMPLETE"	text	NO
B8:B10	Expression	=B8="NOT AVAILABLE"	text	NO
B8:B10	Expression	=B8="AVAILABLE"	text	NO

 

[steve400243]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Vessel:	Container:	Master Bill:
2	APL QINGDAO	OOLU123456789	COSU6363636363
3
4	Packet Received:	ETA Date:	Container Released:	Container Received	Devanned On:	Storage 7th Day:	Storage 12th Day:
5	9/1/2021	9/1/2021	9/3/2021	9/3/2021	9/3/2021	9/9/2021	9/14/2021
6
7	Housebill:	Status:	Office:	Customer:	Mode:	Cartons:	CBM:	Customs Release:	OBL Notification:	Entry:	DO Received:	Pickup Date:	Released to:	PRO:	Shipper PLTS	WHSE PLTS
8	ABCD123456789	AVAILABLE	SEA	ABC CORP	FMM	15	10.5	9/4/2021	9/4/2021	799-12345678	9/4/2021
9	ABCD123456790	NOT AVAILABLE	SEA	ABC CORP	FMM	15	10.5	9/4/2021	9/4/2021
10	ABCD123456791	COMPLETE	SEA	ABC CORP	FMM	15	10.5	9/4/2021	9/4/2021	799-12345678	9/4/2021	9/4/2021	Truck	12345678-9	5	5
Sheet7

Cell Formulas
Range	Formula
F5	F5	=E5+6
G5	G5	=E5+11
B8:B10	B8	=IF(SUMPRODUCT(--(C8:P8<>""))=14,"COMPLETE",IF(SUMPRODUCT(--(H8:K8<>""))=4, "AVAILABLE","NOT AVAILABLE"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8:B10	Expression	=B8="COMPLETE"	text	NO
B8:B10	Expression	=B8="NOT AVAILABLE"	text	NO
B8:B10	Expression	=B8="AVAILABLE"	text	NO

Thank you. I will check it out tomorrow. Decided to take a long weekend away!

 

[steve400243]

Thank you. I will check it out tomorrow. Decided to take a long weekend away!

Thank you JGordon - I appreciate the help! Works as needed.

 

[smozgur]

Thank you JGordon - I appreciate the help! Works as needed.

This means post #2 is the answer and it should be marked as the solution, but you accidentally marked your last post as the solution. So I changed the marked solution accordingly.

In the future, please mark the post as the solution that answered your question. You can also mark your own post as the solution as long as you answer your own question.

 

[steve400243]

This means post #2 is the answer and it should be marked as the solution, but you accidentally marked your last post as the solution. So I changed the marked solution accordingly.

In the future, please mark the post as the solution that answered your question. You can also mark your own post as the solution as long as you answer your own question.

Thank you making that correction. I will mark correctly in the future!

 

[smozgur]

Thank you making that correction. I will mark correctly in the future!