Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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

0

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.

Answer
Discuss

Answers

0

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:

=NOT(ISBLANK(B1))

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

=IF(ISBLANK(B1),YOUR_ORIGINAL_FORMULA_HERE,FALSE)
Discuss


Answer the Question

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