Hacer que los dos puntos se pongan automáticamente en una hora en excel

Así quedaría tu rutina.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim celda As String
Dim horas As String
Dim minutos As String
Dim segundos As String
celda = Target.Value
horas = Left(celda, 2)
minutos = Mid(celda, 3, 2)
segundos = Right(celda, 2)
celda = horas & ":" & minutos & ":" & segundos
'para que no se vuelva a ejecutar el change
Application.EnableEvents = False
Target = celda
Application.EnableEvents = True
End Sub

Le quité la línea del offset porque eso dependía de la dirección del enter (estás asumiendo que es a derecha pero si fuera hacia abajo ya no te sirve esa línea). Mejor utilizamos Target, que es la celda modificada, y allí se coloca el resultante.

Sdos. No olvides finalizar la consulta.

Hola experto gracias por tu respuesta me funciona bien excepto por algo, las horas en la mañana no funcionan desde el medio si osea al yo poner 120023 que seria 12:00:23 funciona perfecto pero al poner una hora de la mañana por ejemplo 092334 se convierte a esto 20:33:34 y asi con todas otra por ejm 001223 se convierte a 12:12:23 pm teniendo que convertirse a 00:12:23 am espero que me puedas ayudar que tengas un buen dia

Señora Elsa disculpa molestarte tanto, no se si quizá sea posible asignar este macro o formula unicamente a la columna B que es en la que utilizo las horas, en caso de no ser posible o que sea muy difícil que funcione en toda la hoja de nuevo agradezco mucho tu colaboración que tengas un buen dia

Te agregué el control de la col B y también el control del largo del dato ingresado. Si es > 6 o < 4 no se ejecuta (podes agregar un mensaje de aviso).

Es decir que si ingresas 001225 se consideran 4 dígitos y se resuelve de modo diferente a si el dato ingresado es 011225 (5 dígitos) o 122345 (6 dígitos).

No sé si quieras agregar la opción de que se ingresen 3 caracteres (125 = 0:01:25) o 2 caracteres (50 = 0:00:50).

Si se pueden presentar estas opciones tendrás que agregarlas. Te dejo el tema como tarea.

Private Sub Worksheet_Change(ByVal Target As Range)

'x Elsamatilde

Dim celda As String
Dim horas As String
Dim minutos As String
Dim segundos As String
'solo controla la col B
If Target.Column <> 2 Then Exit Sub
'controla el largo del dato
If Len(Target) < 4 Or Len(Target) > 6 Then Exit Sub
celda = Target.Value
If Len(Target) = 4 Then
horas = 0
minutos = Mid(celda, 1, 2)
segundos = Right(celda, 2)
ElseIf Len(Target) = 5 Then
horas = Left(celda, 1)
minutos = Mid(celda, 2, 2)
segundos = Right(celda, 2)
Else
horas = Left(celda, 2)
minutos = Mid(celda, 3, 2)
segundos = Right(celda, 2)
End If
celda = horas & ":" & minutos & ":" & segundos
'para que no se vuelva a ejecutar el change
Application.EnableEvents = False
Target = celda
Application.EnableEvents = True

En la Configuración Regional, del Panel de control de Windows, fíjate como considera las horas am de las pm. En mi caso la macro dá perfecto.

Sdos!