How to highlight only the last edited row?

Question

I have a table of customer data in Excel that can be added/edited using a VBA userform. Therefore I would like to highlight the row that has just been added/edited. This is my first time using VBA so I searched and found code from here:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Value <> "" Then
        Target.Interior.ColorIndex = 6
    End If
End Sub

which works perfectly fine but the highlights for the previous edits/add-ons are still there. I just want the last one to be highlighted.

Answer 1

Use a variable. Store the range in that when you are changing the color. Next time remove the color from that range.

Is this what you are trying?

Dim prevRng As Range

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim aCell As Range

    If Not prevRng Is Nothing Then prevRng.Interior.ColorIndex = xlNone
    Set prevRng = Target

    For Each aCell In Target
        If aCell.Value <> "" Then aCell.Interior.ColorIndex = 6
    Next aCell
End Sub

This will handle multiple cells as @Pᴇʜ mentioned in the comment.