Is there a way to make Excel highlight text changes with a specific font color?

Question

I want Excel to track text changes I'm making in a sheet and highlight them with a different font color (i.e. green in this case). For example, if cell A1 changes from "John Fred" to "John Bosco", I want "John Bosco" to turn to green (font).

I'm aware of the "Track Changes" feature of Excel but this doesn't give me the option to change font color and it uses comments to track changes, which I don't want. Is there a function/formula or VBA code that can do the job, so that all cells with changed or added text will have green text?

I'm also aware of Conditional formatting, but this option doesn't seem to match what I'm looking for. Maybe there's a formula I can use in Conditional formatting for Excel to dectect text changes and highlight them with a different font color.

Here is the VBA code I found online but it's not working. What am I doing wrong?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim OldValue As String
    Dim NewValue As String
    Dim Cell As Range

    For Each Cell In Target
        If Cell.Value <> "" Then
            If Cell.Value <> Cell.Value Then
                OldValue = Cell.Value
                NewValue = Cell.Value
                With Cell.Font
                    .Color = vbGreen
                End With
            End If
        End If
    Next Cell

End Sub

Please note that I'm using Microsoft Office Professional Plus 2019, desktop version. This is my first question on this platform, so pls bear up with me for any mistakes.

Answer 1

Your code only executes If Cell.Value is different from Cell.Value (the line If Cell.Value <> Cell.Value Then) which is never true, so your code will never execute. Also the OldValue and NewValue variables unfortunately don't do anything. All you need for your code to work is remove the if statement. Correct code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    For Each Cell In Target
        If Cell.Value <> "" Then
            With Cell.Font
                .Color = vbGreen
            End With 
        End If
    Next Cell

End Sub

I just realized you probably only want the font change to occur if the content of the cell is changed. This works slightly differently in that even if you enter the exact same thing that was in it before, the event will trigger, changing the font even though the content is the same.

*I found a method which changes the font only when the content changes. You could store the entire worksheet in a variable which is updated when selection changes, the comparison is then done with this variable. New code:

Private shb() As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim OldValue As String
    Dim NewValue As String
    Dim Cell As Range

    For Each Cell In Target
        If Cell.Value <> "" And Cell.Value <> shb(Cell.Row, Cell.Column) Then
            With Cell.Font
                .Color = vbGreen
            End With
        End If
    Next Cell

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Set rng = Range("A1:Z100") 'if your data extends past this range, update this variable so that your data fits into it!
    shb = rng
End Sub