Conditional Formatting

Occasional Contributor



I need some help with a spreadsheet for my client and some conditional formatting.


I've attached a photo of the sheet to show what I'm talking about. I've set it up so when the date in column P is entered then it populates the date 30 days from then in column S, then column V and so on. When the date in column S is is met and up to 7 days overdue then it will highlight orange. When this date is 8 days or more overdue then it highlights red. I cant get the correct formatting to show this so any help would be greatly appreciated. 


The next problem which stems from this one is how do I get rid of the red box. It highlights red to show my client that he needs to get in touch with people every 30 days. So if its overdue and highlights red its to remind him to email but when he does it will still be red. Could I add another column in titled 'Sent' and he could add a Y in there when he sends that email. A formula could then be entered to then show if that box has Y then remove the conditional format. Open to ideas if there's an easier way.


Hope that makes sense and thanks very much!

9 Replies

Hi @KeiraW73 

You may use below formula in conditional formatting to achieve your requirements.


I have added a status column , if this column is filled no Red color will be displayed.

A sample file is also attached for your reference.

Please let me know if it works for you.






This is perfect! Thank you so much for your help! I just couldn't figure out how to combine them both together.


Would you be able to have a look at my sheet and double check my formats for columns J-O. I feel I could condense them like you have but not entirely sure where to start.

It's set up so intro date is added and if it has been more than 30 days the reply column will fill red. If a reply is received and Y added the red will disappear and the next 4 columns show N/A. If a N is entered then the red also disappears and a 2nd intro date is added and so on. There is 3 formats for one cell which seems a lot. Hope that makes sense!


Thank you!!!!

I forgot to attach the sheet!

Hi @KeiraW73 


I have tried to modify the logic as per your explanation and that gives below results:


You may refer the attached file. 



Yes that is perfect, thank you so much.

Last question, I promise! Is there a way to have the 1st email column (H) auto populate with a date? It will be a pain as it would need to take the date from either intro 1, 2 or 3 which ever had the latest date in it. So the first row it would be intro 1 as the rest are N/A. Second row it would be intro 2 and so on.

Hi @KeiraW73 


No issues, you may ask as many questions as required. As far as column H is concerned please use below formula:




You may also refer to the attached file.





Ok thank you. Only issue is I need the 1st email date to be 30 days later than the dates before it.



Simply add 30 at the end of the formula =MAX(B5,D5,F5)+30




Thank you so much for your help! This is brilliant