Conditional formatting cell based on other cell text

[Grictori]

Ok need some help here. I had another thread that is basically answered, but this is kind of different so I thought I would just pose the question on a new thread. I have a task tracker spreadsheet with various cells containing conditional formatting. This is the final requirement for my workbook. I will attach an image as reference. I might also add that I don't think I can add in an "in cell" formula to solve this as it already has one for that column.
Here are the finer points of the sheet:

• there is a start date and a due date that populates a status column with "late", "Ahead of schedule", "on Schedule" etc.
• There is a column with "completed date" that turns the entire row grey when there is a date in that column
• unfortunately, when the complete date is entered it does not change the status to complete in the status column.

F​	G​	H​	I​	J​	K​	L​	M​
Start Date​	Due Date​	% Progress​	Completed Date​	Remarks​	Required Progress​	Status​	Days Remaining​
2/15/23	3/8/23					Late	-1
3/1/23	4/18/23					Ahead of Schedule	40

Here is what I need if possible:

• IF column I is populated with a date then make column L state Complete.
• IF column L states Complete then fill cell as Grey (the other status titles have a fill color)
• I believe it has to be executed in the conditional formatting function as those cells already have a formula =IF(M10="","",LOOKUP(M10,{-999,1,6,10},{"Late","At Risk","On Schedule","Ahead of Schedule"}))

 

[Wookiee]

Try using this formula for your cells in Column L (assuming a header row and data starts on Row 2).

=AND($L2="Complete",$I2<>"")

 

[Wookiee]

Try using this formula for your cells in Column L (assuming a header row and data starts on Row 2).

Guess I should have clarified that that was the formula for the Conditional Formatting, as you obviously have a formula in Column L.