Excel conditional formatting with multiple status updates

Question

I'm sure this is covered elsewhere but I guess I wasn't sure about which keywords to search.

I've got a spreadsheet with different tasks in one column.

In the next column I have the latest update from a dropdown list ("awaiting response", "awaiting approval", "on hold", "complete", and a few others).

row 1 | TASK 01 | ON HOLD  
row 2 | TASK 02 | AWAITING RESPONSE  
row 3 | TASK 03 | ON HOLD  
row 4 | TASK 02 | AWAITING APPROVAL  
row 5 | TASK 01 | AWAITING APPROVAL  
row 6 | TASK 03 | AWAITING APPROVAL  
row 7 | TASK 02 | COMPLETE 

I have conditional formatting in place so that the "TASK __" cells are red when the status in column 2 states anything but complete. In this case, the task cell in row 7 would no longer be red. However, the task 02 cells in rows 2 and 4 remain red.

How can I use conditional formatting to make ALL of the "TASK 02" cells in column 1 change from red to no color fill when I mark TASK 02 as completed in row 7?

Answer 1

Assuming your current conditional formatting rule is along the lines of

=$B1<>"Complete"

change it to

=AND($B1<>"Complete",COUNTIFS(A:A,A1,B:B,"COMPLETE")=0)

That will only apply the format if none of the tasks with the current row's ID are complete.

Edit: as pointed out in a comment, the first test in the AND() is now obsolete, so the formula is just

=COUNTIFS(A:A,A1,B:B,"COMPLETE")=0

(but not >0 as suggested in the comment to the question).