Highlight Row if a cell is unpopulated by certain date

[Thomo10]

Hi all,

Just started a new role and I need to re-design a checksheet as part of my first project. I would like to be able to put in place a formula/format that would change the colour of an entire row or group of cells on a traffic light basis if a certain cell in the group is unpopulated by a certain number of days before a date which will be populated in another cell.

For example, if cell A1 has a date of 10 Sep 2011 populated, I would like to be able to colour orange cells A2 through A10 orange if cell A2 is not populated by 05 Sep. And then Red if it is still not popluated by 09 Sep onwards.

I hope I have made sense, any help on this would be greatly appreciated.

Thanks
Thomo

 

[Peter_SSs]

Yes they are what I finally end up with.

Cell A2 was showing

Condition 1: =AND(A$2="",TODAY()>=A$1-1)
Condition 2: =AND(A$2="",TODAY()>=A$1-5)


Cell B2 was showing

Condition 1: =AND(B$2="",TODAY()>=B$1-1)
Condition 2: =AND(B$2="",TODAY()>=B$1-5)

And so on down the line until I fixed all formulas individually to look at cells A1 & A2

In that case I don't understand how the formulas in B2 are "looking at cells A1 and A2"

Edit: I guess it doesn't really matter so long as you have it going how you want.