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]

Welcome to the MrExcel board!

Select A2:A10 and apply the Conditional Formatting shown. If you need further help, please advise which version of Excel you are using.

Excel Workbook
	A
1	17/07/11
2
3
4
5
6
7
8
9
10
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =AND(A$2="",TODAY()>=A$1-5)	Abc
A2	2. / Formula is =AND(A$2="",TODAY()>=A$1-1)	Abc

 

[Thomo10]

Thanks Peter,

It doesn't seem to be acting the way I need it to though. My version is 2003.

I have a date in A1 and it seems to colour the cells amber accross the selection as per the rule, however the red format doesn't seem to kick in a day before/on and after the date in A1. It seems to ignore the red format completly

Also, If I populate A2, only other cells in the selection that are populated return to white. If the cells are empty, they stay amber even if A2 is populated.

Hope this makes sense, I appreciated your help.

Simon

 

[Peter_SSs]

Thanks Peter,

It doesn't seem to be acting the way I need it to though. My version is 2003.

I have a date in A1 and it seems to colour the cells amber accross the selection as per the rule, however the red format doesn't seem to kick in a day before/on and after the date in A1. It seems to ignore the red format completly

Also, If I populate A2, only other cells in the selection that are populated return to white. If the cells are empty, they stay amber even if A2 is populated.

Hope this makes sense, I appreciated your help.

Simon

OK, for Excel 2003 the first thing is that Condition 1 should be the red condition and Condition 2 should be the amber condition so ensure you have that order.

As to your description of what happens when A2 gets populated, I'm not sure whether you have described what happens with the formatting I suggested or what you want to happen.

Please try to clarify what yoy want to happen when A2 (or A3, A4 etc) is populated.

Try dropping the $ sign from the A$2 cell address.

 

[Thomo10]

I'm afraid it just seems to be igonoring the amber format now.

With regards to what I want to happen. If A1 has date of 10th sep, I want A2:A10 to turn Amber on 5th Sep, and then to red on 9th Sep-onwards, ONLY if nothing is populated in cell A2. As soon as I have populated A2 with a "yes" for example, I would like all cells in the selection to return to white.

At the moment, what seems to happen is that A2 returns to white but the other cells are remaining red (even if the date is not within the 5 days)

Thanks
Simon

 

[Thomo10]

Scrap that, I have got it to work, the only thing is that I have to format every cell in the selection individually as it seems if I highlight as a group then it is picking up A3 A4 ect as opposed to concentrating on A2 as a constant. But that's fine and it is doing what I want now.

Thank you very much for your help.

Simon

 

[Peter_SSs]

Well, you have it working now but you should not have had to do each cell individually.

What CF formulas did you end up with in A2?
Condition 1:
Condition 2:

What CF formulas did you end up with in A3?
Condition 1:
Condition 2:

 

[Thomo10]

Well it may have been my fault because I actually wanted the range of cells to go across so A2:I2 as opposed to down (A2:A10) but I applied the same instructions thinking this would have the same effect.

So in response to your reply:

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

Thanks
Simon

 

[Peter_SSs]

Are they the formulas you finally ended up with? They don't seem to match what you were describing in post #5 (even allowing for the swith from column to row).

 

[Thomo10]

Yes they are what I finally end up with.

Ignore the first paragraph in post 5, I realised I had entered the formula incorrectly, so the only issue there was that each cell in the selection was looking to the incorrect cells for date and to be populated.

Simon