Conditional Formatting for dates

JMO - Sep 10, 2015 at 05:50 PM

Hey there, so what I am trying to do is:

I have a range of fruits that we grow. All of the fruits have to be tested at certain times. A new one has to be tested every 21 days till it has completed 4 tests. After those 4 tests it only needs to be tested every 4 months. I need the cell to change color (red) when we are 5 days away from its 21 day or 4 month mark. I want it to stay that color (red) until a new number is added to the next test column even if it is after the deadline. l found this formula =(AND(ISBLANK(D5),((C5+21)<(TODAY()+5)))) works for individual cells however I can not get it to work for a whole column.