Stop conditional formatting of a cell once data is entered into another cell


I have set up conditional formatting in a spreadsheet tracking dates and deadlines for a project. 

I want the current format of cells in column A to stop once I have a date entered in column B.  Column A is the date a project is due.  I have formatting set up to reflect green if the date is earlier than 7 days from "today", red for equal to or later than "today", and orange between "today+1" and "today+6".  Column B contains the date the project was actually completed.

I want the conditional formatting of column A to stop once a date is entered into column B and no longer format the background.

The current formula's I have set up in column A are: 1) Cells containing blanks have no color in the background.  2) Cell Value between =TODAY()+1 and +TODAY()+6 have an orange background reflecting the due date is within 7 days from today's date.  3) =$H7>TODAY()+7 have a green background reflecting the due day is 7 or more days from the due date  4) =$H7<=TODAY() have a red background is past the due date.

I'm fine with the background of the cells in column A going back to white once a date is entered in column B since I no longer need to track that date.

Any help or suggestions would be greatly appreciated.




You can do one of two things:

1.) Create a new rule and set the background for that rule to white and maybe setup some borders for it in the formatting and use this formula:


2.)Add an IF statement check to your exisiting formulas like this:


Answer the Question

You must create an account to use the forum. Create an Account or Login