I managed to sort it out myself.

I removed the code from the "Worksheet Open" Activate event and move
it into a Module and made a call to the Module on opening th
worksheet.

Used this code:

Dim dt As Date

' If day = Monday
If Weekday(Date, vbSunday) = 2 Then
dt = Date + 4
dWeekEnding = Format(dt, "dd.mm.yyyy")
End If
Day1Date = Format(dt - 4, "dd.mm.yyyy")
Day2Date = Format(dt - 3, "dd.mm.yyyy")
Day3Date = Format(dt - 2, "dd.mm.yyyy")
Day4Date = Format(dt - 1, "dd.mm.yyyy")
Day5Date = dWeekEnding

Not pretty and probably bad programming coz i guess i could have used
For - Next loop, but still, it does the job

--
Message posted from http://www.ExcelForum.com