Conditional Formatting

[yhatzee]

Hi

Can anyone help with conditional formatting?

I have Cell E7 with a date in it and cell F7 that calculates a date 2 years from the date in E7.

I need conditional formatting to turn cell F7 Red if E7 is blank and also if the date in F7 has past. I also what the cell F7 to be white if the date has not yet past.

I have tried =OR($E$7="",$F$7<TODAY())

for some reason it is keeping the cells red when the date has not past, but also when i drag the format down the column it loses formatting?

Any help is greatly appreciated

Thanks

S

 

[pjmorris]

Highlight the data you want to format (Click in cell E7 and drag down - that step is actually quite important). I assume from your example the data starts in Row 7. Then use the following formula:

=OR($E$7="",$F7<TODAY())

The problem is because you've used absolute references to the row and hence the formatting is referring to row 7 even if its formatting row 100000!

HTH