Conditional formatting-have the formula increment to test the relevant rows

Hi everyone,

I'm trying to apply conditional formatting which checks the current date and then looks at the date some work should be completed, highlighting the cell with the due date (D5) in yellow as a reminder.

I've used the formula - =AND($K5="",$D5>"", (TODAY()-$D5)>-3)

And it works just fine on row 5.

But I want to copy the conditional formatting to the next 200 rows so that each formula refers to the data in the relevant row - 6, 7, etc. up to 204.

Whether I copy and paste or use format painter down the column, the formula keeps referring to row 5.

How do I get the formula to increment the row number in the formula to test each relevant row's data?

Thanks

Thanks for such a quick response.

I've attached a sample spreadsheet. Trial Conditional formatting.xlsx

The concept is that if the work does not have (in column G) a date completed, the formula checks to see the due date in column D and if that has already passed, sets a red colour. If it is less than three days away, it sets a yellow colour.

But, when I look at the formula for cells other than the one I started with – D5, they all have references as if they are checking row 6.

However, if you look at the actual colours that are set in the cells, the formula seems to be working! That is, overdue dates are red, dates less than three days away our yellow and ones other than that aren't coloured.

It's nice that it's working – but somewhat disconcerting that the formula doesn't seem to reflect what it should do, leaving me in a bit of a quandary.

Tim

OK - I've now got the same formula covering D5 to D204. And I've added in the suggestion of Sixthsense.

And it's all working - thanks, Fotis,( again)!

But - and this is probably due to my lack of understanding of Excel conditional formatting - why does each line of column D have the same formula- =AND($G5="",$D5<>"", TODAY()-$D5>0)?

I expected that the formula would change line number as it progresses down the sheet.

Is this due to specifying the range as $D$5:$D$204 - following which Excel states the first line's formula in the formula bar, but applies the current line number when calculating?

It's now a question solely to improve my knowledge, but if you have time, it would be greatly appreciated.

Tim

Aaaah - thank you Fotis; once again you've helped solve my problem – and I've learnt more about Excel.

I hope you have a great day.

Tim

Aaaah - thank you Fotis; once again you've helped solve my problem – and I've learnt more about Excel.

I hope you have a great day.

Tim