Excel 2007 : Lookup last Date in a row

I was wondering if someone could help me.
I am trying to design a drawing register that shows the status of a drawing.
What I need to be done is
What I need I Column CQ “status”
To pick up the last date.
Example Below

Target Date to Red Line Redline Prepared Redline Checked Date Sent to Designer Date Received from Designer As Built Checked Re-issue to WSP Date Revision Received from Designer Status
21/05/2011 03/05/2011 03/05/2011 03/05/2011
31/05/2011 03/05/2011 28/05/2011 28/05/2011


I have tried several formulas
=INDEX(CI15:CP15,1,5)
=IF(D17="","",(LOOKUP("xx",CI17:CP17)))
=IF(ISBLANK(J15),I15,IF(ISBLANK(K15),J15,IF(ISBLANK(L15),K15,IF(ISBLANK(M15),L15,IF(ISBLANK(N15),M15,IF(ISBLANK(O15),N15,IF(ISBLANK(P15),O15)))))))

The IF Statement((D17="","",(LOOKUP("xx",CI17:CP17)))) does work only if it has Words
And the other two don’t seem to like gaps in the range.
After this is done I want to do some conditional formatting with the status cells.

To Yellow 7days before the date Orange 7 days after date and Red 14 days after day.

Can anyone help me.