Dear all,

I am looking for help to complete this macro and stuck at one point.

I have a input sheet which contains some data. What i need to do is to serach for the expired course, highlight with red and blue color 14 days before expired. The name of staff, course name and expired date will be automatically send to designated email account. That's what i have finished in these days. However, i 'm stuck with the problem that i dont know how to transpose the Expired staff name, course and date to the other worksheet without any duplication of the couse and date.

I have attached a copy of the file with what i have managed so far.

Very grateful for anyone’s helptest MandatoryTraining.xlsm

Kevin