Conditional Formatting help

Occasional Visitor

Hi,  Here's the problem.  

 

We schedule Dr. visits.  After an appt is scheduled, there are 'To Do' items, such as a post quality follow-up call (B), or documents uploaded (C), etc...  I would like to create reminders for the staff after the appointment has finished.  

 

Assuming the Appt Date is in Column A and the others (B,C,D) are the tasks to be completed after the appt day arrives, I would like B,C,D to be highlighted thereafter.  

 

Lastly, once B, C, D are individually entered into, I would like their conditional formatting to stop. 

 

Thank you for your help and lmk if you need additional info. 


Scott A. 

 

1 Reply

@andermanrn2180 So in the attached I give an example and added 2 rules. 1 yellow for items <10days old and 1 orange for >10days old as an example of something you might want to add.  here is the basic formula (Conditional Formatting -> New Rule -> Custom Formula):

=($A1<>"")*(B1="")*($A1<TODAY()-10)

So the $A says to always lookin in column A but the 1 may and will change to whatever row it is checking

The B1 is checking the top left corner of the selected range (B:D) to see if that cell is empty and then excel will go to the next cell and increment that reference accordingly

The <TODAY()-10 is checking if the entered date is more than 10 days ago.

Hope this is helpful and good luck.