VBA: Script to change text color if a change is made

[hector.febus]

Hello,

I have this code (see below), the code changes new text entered to blue, but as soon as you click enter, the text in the cell is treated as old, so if you go again into the cell and make another change, it will change the text to black and only the new change to blue. I need it to continue changing to blue and keep recent changes in blue.

Dim oldText As String
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oldIndex As Integer
    Dim newIndex As Integer
    Dim rng As Range
    
    oldIndex = 1
    Set rng = Me.Range("A9:L300")
    
    If Target.Text <> "" And Not Intersect(Target, rng) Is Nothing Then
        For newIndex = 1 To Len(Target.Text)
            If Mid(Target.Text, newIndex, 1) = Mid(oldText, oldIndex, 1) Then
                Target.Characters(newIndex, 1).Font.ColorIndex = 1
                oldIndex = oldIndex + 1
            Else
                Target.Characters(newIndex, 1).Font.ColorIndex = 5
            End If
        Next newIndex
    End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
        oldText = Target.Text
    End If
End Sub