Saber cual fue la ultima celda modificada en una fila dentro de un rango de celdas.

Creo que la siguiente macro resuelve todas las situaciones. Se coloca en el objeto HOJA donde vayas a trabajar y se controla el rango original, es decir A1:E3

En este caso utilizo además de la col F, la col H para ir guardando en una cadena el orden de las col donde se colocan valores > 3 caracteres de largo.

Private Sub Worksheet_Change(ByVal Target As Range)
'x Elsamatilde
'se controla lo ingresado en rango A1:E3
If Intersect(Target, Range("A1:E3")) Is Nothing Then Exit Sub
'si se modifican varias celdas (x ej para borrarlas) no se ejecuta
If Target.Count > 1 Then Exit Sub
'se mira en la cadena, si estaba se la quita
For i = 1 To 5
    If Val(Mid(Cells(Target.Row, "H"), i, 1)) <> Target.Column Then cadena2 = cadena2 & Mid(Cells(Target.Row, "H"), i, 1)
Next i
Cells(Target.Row, "H") = cadena2
'si no es mayor que 3 deja en F1 el último valor de la cadena
If Len(Target.Value) <= 3 Then
    Cells(Target.Row, "F") = Right(Cells(Target.Row, "H"), 1)
Else
    Cells(Target.Row, "F") = Target.Column
    Cells(Target.Row, "H") = Cells(Target.Row, "H") & Target.Column
End If
End Sub

Hola Elsa buen día, la macro me ha funcionado muy bien gracias, podrás sobre esta misma pregunta ayudarme para finalizar con lo siguiente:

Como se puede modificar la macro para que en lugar de mostrarme el # de columna me traiga el valor de la ultima celda modificada cronológicamente.

Te agradezco mucho tu tiempo.

Saludos

Private Sub Worksheet_Change(ByVal Target As Range)
'x Elsamatilde
'se controla lo ingresado en rango A1:E3
If Intersect(Target, Range("A1:E3")) Is Nothing Then Exit Sub
'si se modifican varias celdas (x ej para borrarlas) no se ejecuta
If Target.Count > 1 Then Exit Sub
Dim x As Byte, i As Byte
'se mira en la cadena, si estaba se la quita
For i = 1 To 5
    If Val(Mid(Cells(Target.Row, "H"), i, 1)) <> Target.Column Then cadena2 = cadena2 & Mid(Cells(Target.Row, "H"), i, 1)
Next i
Cells(Target.Row, "H") = cadena2
'si es mayor que 3 agrega en H1 el nro de col
If Len(Target.Value) > 3 Then Cells(Target.Row, "H") = Cells(Target.Row, "H") & Target.Column
'según el último caracter en H será el texto en F
If Len(Cells(Target.Row, "H")) = 0 Then
    Cells(Target.Row, "F") = ""
ElseIf Len(Cells(Target.Row, "H")) = 1 Then
    Cells(Target.Row, "F") = Cells(Target.Row, Cells(Target.Row, "H"))
Else
    x = (Right(Cells(Target.Row, "H"), 1))
    Cells(Target.Row, "F") = Cells(Target.Row, x)
End If
End Sub

Sdos!

¡Gracias! Elsa, excelente respuesta. que tengas un buen día.

Saludos

Buen día nuevamente Elsa, he intentado trasladar la macro a un ejemplo más real pero al modificar la 4ta columna me marca error, y en AF no me muestra el valor, en el For i = el valor de i se desborda del contador, es decir en lugar de que i = 4 es i =6, te dejo la macro que la he movido del rango A1:E3 al rango AA1:AE3

Private Sub Worksheet_Change(ByVal Target As Range)
'x Elsamatilde
'se controla lo ingresado en rango A1:E3
If Intersect(Target, Range("A1:E3")) Is Nothing Then Exit Sub
'si se modifican varias celdas (x ej para borrarlas) no se ejecuta
If Target.Count > 1 Then Exit Sub
Dim x As Byte, i As Byte
'se mira en la cadena, si estaba se la quita
For i = 1 To 5
    If Val(Mid(Cells(Target.Row, "H"), i, 1)) <> Target.Column Then cadena2 = cadena2 & Mid(Cells(Target.Row, "H"), i, 1)
Next i
Cells(Target.Row, "H") = cadena2
'si es mayor que 3 agrega en H1 el nro de col
If Len(Target.Value) > 3 Then Cells(Target.Row, "H") = Cells(Target.Row, "H") & Target.Column
'según el último caracter en H será el texto en F
If Len(Cells(Target.Row, "H")) = 0 Then
    Cells(Target.Row, "F") = ""
ElseIf Len(Cells(Target.Row, "H")) = 1 Then
    Cells(Target.Row, "F") = Cells(Target.Row, Cells(Target.Row, "H"))
Else
    x = (Right(Cells(Target.Row, "H"), 1))
    Cells(Target.Row, "F") = Cells(Target.Row, x)
End If
End Sub

Saludos

Corrección, te adjunto nuevamente el código

Private Sub Worksheet_Change(ByVal Target As Range)
'x Elsamatilde
'se controla lo ingresado en rango A1:E3
If Intersect(Target, Range("AA1:AE3")) Is Nothing Then Exit Sub
'si se modifican varias celdas (x ej para borrarlas) no se ejecuta
If Target.Count > 1 Then Exit Sub
Dim x As Byte, i As Byte
'se mira en la cadena, si estaba se la quita
For i = 1 To 5
    If Val(Mid(Cells(Target.Row, "AH"), i, 1)) <> Target.Column Then cadena2 = cadena2 & Mid(Cells(Target.Row, "AH"), i, 1)
Next i
Cells(Target.Row, "AH") = cadena2
'si es mayor que 3 agrega en H1 el nro de col
If Len(Target.Value) > 3 Then Cells(Target.Row, "AH") = Cells(Target.Row, "AH") & Target.Column
'según el último caracter en H será el texto en F
If Len(Cells(Target.Row, "AH")) = 0 Then
    Cells(Target.Row, "AF") = ""
ElseIf Len(Cells(Target.Row, "AH")) = 1 Then
    Cells(Target.Row, "AF") = Cells(Target.Row, Cells(Target.Row, "AH"))
Else
    x = (Right(Cells(Target.Row, "AH"), 1))
    Cells(Target.Row, "AF") = Cells(Target.Row, x)
End If
End Sub

Si tienen la hermosa herramienta que les permite dejar 'muestra' de la hoja...¿por qué muestran algo que luego no se condice con lo real? ¿Por qué perder el tiempo así?

'se controla lo ingresado en rango A1:E3
If Intersect(Target, Range("A1:E3")) Is Nothing Then Exit Sub

Allí indica tu rango REAL.

For i = 1 To 5
    If Val(Mid(Cells(Target.Row, "H"), i, 1)) <> Target.Column Then cadena2 = cadena2 & Mid(Cells(Target.Row, "H"), i, 1)
Next i

La variable i se moverá desde la primer a última col del rango REAL, o sea averiguá cual es AA y cual es AE.

Si aún quedan más detalles...

Hola Elsa gracias, lamento lo de la herramienta no utilizada; ahora bien he aplicado For i = 27 to 31, ya que esas son las columnas que corresponden, pero el resultado es el mismo.

buen día y gracias nuevamente

Buen día Elsa, he estado intentando encontrar la solución al error al adaptar la macro a mi hoja, pero aun no he podido, lo unico que he detectado es que al momento que la macro me marca error y se detiene, el error me lo arroja en: 

Else
    x = (Right(Cells(Target.Row, "AH"), 1))
     Cells(Target.Row, "AF") = Cells(Target.Row, x)
End If
End Sub

Y al pasar el cursor por encima de la condición For i =

si lo tengo como For i = 1 to 5

dice i = 6

o si lo tengo de acuerdo a la posición de las columnas AA:AE

For i = 27 to 31

dice i = 32

la macro funciona perfectamente como se planteo de A1:E3, pero al adaptarla al rango que se ocupa modificando todos los parámetros correspondientes, ya no funciona al 100 y me marca error, solicito de tu ayuda y te agradezco tu tiempo .

Saludos

Lo que corresponde es que i vaya desde la col AA hasta AE, por lo tanto será:

For i = 27 to 31   por lo tanto i nunca llega a 32.  Pero por las dudas que en algún momento extiendas tu rango declará las 2 variables como Integer en lugar de Byte.

Pero ahora por no debe ser de 1 dígito sino de 2... en H se guardan 2 dígitos para cada nro de columna.

'se mira en la cadena, si estaba se la quita
For i = 1 To 5
    If Val(Mid(Cells(Target.Row, "AH"), i, 1)) <> Target.Column Then cadena2 = cadena2 & Mid(Cells(Target.Row, "AH"), i, 1)
Next i
Cells(Target.Row, "AH") = cadena2
'si es mayor que 3 agrega en H1 el nro de col
If Len(Target.Value) > 3 Then Cells(Target.Row, "AH") = Cells(Target.Row, "AH") & Target.Column
'según el último caracter en H será el texto en F
If Len(Cells(Target.Row, "AH")) = 0 Then
    Cells(Target.Row, "AF") = ""
ElseIf Len(Cells(Target.Row, "AH")) = 2 Then
    Cells(Target.Row, "AF") = Cells(Target.Row, Cells(Target.Row, "AH"))
Else
    x = (Right(Cells(Target.Row, "AH"), 2))
    Cells(Target.Row, "AF") = Cells(Target.Row, x)
End If

Sdos!

Interesante, te comparto las pantallas del error, ya cambie el tipo de las variables a Integer. En la imagen te muestro el cursor del mouse sobre la parte que dice For i = 27 To 31, y veras que dice i = 32. Y como podrás ver también en la imagen de abajo, la columna AF no muestra ningún resultado en ningún intento.