Conditional formatting

Question

Hello, I have an excel sheet where I want to include the following formulas:

1. to highlight the row red when the date in column G is past the deadline date.
2. to highlight the row orange when the date in column G is within 7 days from reaching the deadline date.
3. to override the previous rules and highlight the row green when a date of completion is entered in column L but keeps the cell in column G highlighted as red or orange depending on the deadline date.
4. to keep blank cells from highlighting with any of these rules.

Answer 1

Change A2 to $G2 in the formulas in the bottom three rules:

Answer 2

Hello, I did exactly what you mentioned except i changed it from 2 to 50 and I get this leaving some cells with no fill blank spots vary from column J to N.

If you could further assist me that would be great!

It did however keep column G red and green regardless of the green formula.

column G is the first column from left to right with dates.

Thank you!

Answer 3

Let's say you want to apply this to rows 2 to 100.

Select these rows. A2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND(A2<>"", $G2<=TODAY()+7)

Click Format...
Activate the Fill tab.
Select orange as fill color.
Click OK, then click OK again.

Repeat these steps, with the formula

=AND(A2<>"", $G2

and red as fill color.

Repeat them again, with the formula

=AND(A2<>"", $L2<>"")

and green as fill color.

Now, select G2:G100. G2 should be the active cell.

Repeat the steps, with the formula

=AND($G2<>"", $G2<=TODAY()+7)

and orange, and finally with

=AND($G2<>"", $G2

and red.

Answer 4

Column G is the deadline and yes to current date.

Answer 5

Which column contains the deadline? Or is column G the deadline, and should we compare it to the current date?