highlight payment delays

Please manually mock up what you want to see and post again. Shade cells manually. Include some examples that do not meet the criteria.

Thanks. Now please wait patiently. There is no need to (and the rules say you should not) pester members via private message.

Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you

hi kasun,

To color cells you use conditional formatting.
Since your example is dated in the past I have used a help cell in M1 to determine overdue lines

I have put the following formula in Conditional formatting of the range M4:M9

PHP Code: 

=NOT(ISNUMBER(HLOOKUP(DATE(YEAR($M$1);MONTH($M$1)-1;1);$A$4:$L$9;ROW()-3))) 


depending on your local pc settings you might want to use , (comma) instead of ; (semi-colon) as separators in the formula

This formula finds the column 1 month earlier then the first of month of the date in M1 and tests if that cell as any value

lets breakdown the formula starting with the HLookup
1: DATE(YEAR($M$1);MONTH($M$1)-1;1)> this determines the date of the column to find
2: $A$4:$L$9 > is the lookup range to find the column
3: ROW()-3 > this helps find the rownumber of the currentline in the range A4:L9.

the ISNUMBER() tests if there is a number in the found cell. When there is no number in the cell of june 18 then the outcome is FALSE. But because conditional formatting triggers on TRUE statements you use NOT() formula to change FALSE to TRUE
simularly if there is a number then the not() formula will revert the TRUE to FALSE


____________
sidenote: I ignored the merged cells row, even though in this example the outcome is correct, merged cells will cause problems, also the formula does not take quarterly payments into account as there is no field to distinguish if there is a montly or a quarterly payment schedule in place.