you are the one that said:

To complicate matters I would only want to format the cell to
display leading zeros as a last resort.

I guess you mellowed in the last few minutes.

--
Regards,
Tom Ogilvy

"Andy" wrote in message
...
Thanks that seems to have fixed it. However I tackled it in a different
manner. I used Worksheets("Sheet2").Cells(PasteRow, 16).NumberFormat =
"0###")
Andy

"Tom Ogilvy" wrote:

you are placing the time value as a number, or at least it is getting
converted to a number. you can maintain it as a string to see the
leading
zero

Worksheets("Sheet2").Cells(PasteRow, 16) = "'" & Format(DepTime, "HH" &
"mm")

--
Regards,
Tom Ogilvy

"Andy" wrote in message
...
I have a problem where I am copying data from one worksheet to another
and
I
am also changing the time format from HH:mm to HHmm. To do this I am
using
this code.

Worksheets("Sheet2").Cells(PasteRow, 16) = Format(DepTime, "HH" &
"mm")

The problem is that the hour figure does not show the leading zero
even
though I have requested it, HH rather than H. The leading zero is a
requirement. To complicate matters I would only want to format the
cell
to
display leading zeros as a last resort. Any ideas would be most
appreciated