As shown by JE you need to set the numberformat to text eg:

With Cells(1, 7)
.NumberFormat = "@"
.Value = .Text
End With

However, reading you post in excel.misc I would have thought it is the
cells that are a string datatype that you need to worry about not the
ones that are doubles as true excel times are stored as a double not a
string.

Regards
Rowan

Chet Shannon wrote:
Yeah you're right.. I didn't show but I then used a statement of
cells(1,7)=newvar after the and the cell still didn't change from sub-type 5
to sub-type 8. I thought maybe I had to clear out whatever was in cells(1,7)
first and that's why I did the cells(1,7).clear and then copied the newvar
(which is actually sub-type 8) into cells(1,7). But 1,7 is still type 5 not
8 as I wanted. I know I'm doing something fundamentally wrong.

If VarType(Cells(1, 7)) = 5 Then
newvar = CStr(Cells(1, 7))
Cells(1, 7).Clear
cells(1,7)=newvar
End If