Don't try to set the format using the Value property; rather, assign the
date to the Value property directly and then use the NumberFormat property
to set the format....

With Worksheets("Forecast").Cells(ForecastMaxRowNo, 8)
.Value = Date
.NumberFormat = "dd/mm/yyyy"
End With

Change the format pattern to whatever format you actually want.

--
Rick (MVP - Excel)


"Steve" wrote in message
...
Hi I'm having trouble writing the right date format to a cell.

Currently I have tried

Worksheets("Forecast").Cells(ForecastMaxRowNo, 8).Value = Format(Date,
"Short Date")

Worksheets("Forecast").Cells(ForecastMaxRowNo, 8).Value = Format(Date,
"General Date")

Worksheets(sMonth).Cells(MonthTabMaxRowNo, 8).Value = FormatDateTime(Date,
vbShortDate)

Each time it comes out in American format and my regional setting on the
computer are all set to UK

When I put MSGBOX before it to test it seems to be OK. The Cell format
seems
to be OK too.

Any help or suggestions would be appreciated.

Thanks

Steve