You are only setting the value of the cell, not the
format. So when you put the value "0845" into a cell, just
like if you typed it in that way, Excel sees it as a
regular number, not a special time format, and turns it
into 845 (eight hundred forty five).

You need to paste the actual time value (DepTime) into the
cell and then set the cell's format:

Worksheets("Sheet2").Cells(PasteRow, 16) = DepTime
Worksheets("Sheet2").Cells(PasteRow, 16) = NumberFormat
= "hhmm"

K Dales

-----Original 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
.