Puedes aplicar el formato deseado celda por celda o a un rango, ejemplo:
Código VBA:
Ver original' A un rango
Sheet1.Range("A2", "A50000").NumberFormat = "yyyy-mm-dd hh:mm:ss"
' A una celda
Sheet1.Cells(contadorFilas, 1).NumberFormat = "yyyy-mm-dd hh:mm:ss"
' Después asignas el valor y aparecerá ya con el formato aplicado
Sheets("Sheet1").Cells(contadorFilas, 1).Value = fecha