conditional formatting with conditions

[Graham C1600]

I have a table such as below. I want the following conditional formatting conditions to be met.

What	Date	Status
Test	01/01/2023	ongoing
Test 1	18/01/2023	Ongoing
Test 2	11/01/2023	Complete

Scenario 1 - If Cell B2 is = or > than today and Cell C2 is "Ongoing", Cells A2,B2,C2 need to be filled in Red.
Scenario 2 - If Cell B2 is = or < than today and Cell C2 is "Complete", Cells A2,B2,C2 need to be filled in Blue.
Scenario 3 - If Cell B2 is = or < 7 days than today and Cell C2 is "Ongoing", Cells A2,B2,C2 need to be filled in Amber.
Scenario 4 - If Cell B2 is = or < than today by more than 7 days and Cell C2 is "Ongoing", Cells A2,B2,C2 need to be filled in Green.

Hope this makes sense.

Thanks

 

[Sufiyan97]

Try

Change Yellow color to Amber

Book3
	A	B	C	D	E	F
1	What	Date	Status
2	Test	1/1/2023	ongoing
3	Test 1	1/18/2023	Ongoing
4	Test 2	1/11/2023	Complete
5
6
7
8
9
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C4	Expression	=AND($B2>=TODAY(),$C2="Ongoing")	text	NO
B2:C4	Expression	=AND($B2<=TODAY(),$C2="Complete")	text	NO
B2:C4	Expression	=AND($B2-TODAY()<=7,$C2="Ongoing")	text	NO
B2:C4	Expression	=AND($B2-TODAY()>=7,$C2="Ongoing")	text	NO