How to highlight a line in Exel yellow and the cells red when making changes (with more than one change)

Question

I am using the below formula to highlight a row yellow and the cell red when making a change to a cell. The problem is sometimes we want to make more than one change to a row and want all of the cells that had changes to remain red but this formula wipes out the first red cell when changing the second cell in the row. Is there some exception I can add to prevent that already red cell from changing to yellow when the second change is made?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
    Target.EntireRow.Interior.Color = vbYellow
    Target.Interior.Color = vbRed
End Sub

Answer 1

Please try this code.

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

    Dim Cl      As Long                 ' last used column

    With Target
        If .CountLarge = 1 Then
            ' change .Row to longest used row number
            ' if your rows aren't of uniform length
            If Sh.Cells(.Row, "A").Interior.Color <> vbYellow And _
               Sh.Cells(.Row, "A").Interior.Color <> vbRed Then
                Cl = Sh.Cells(.Row, Columns.Count).End(xlToLeft).Column
                Sh.Range(Sh.Cells(.Row, 1), Sh.Cells(.Row, Cl)).Interior.Color = vbYellow
            End If
            .Interior.Color = vbRed
        End If
    End With
End Sub

The above procedure isn't tested. In fact, I wonder why you use the Workbook event. However, I did test the function below and perhaps that's all you need.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Cl      As Long                 ' last used column

    With Target
        If .CountLarge = 1 Then
            ' change .Row to longest used row number
            ' if your rows aren't of uniform length
            If Cells(.Row, "A").Interior.Color <> vbYellow And _
               Cells(.Row, "A").Interior.Color <> vbRed Then
                Cl = Cells(.Row, Columns.Count).End(xlToLeft).Column
                Range(Cells(.Row, 1), Cells(.Row, Cl)).Interior.Color = vbYellow
            End If
            .Interior.Color = vbRed
        End If
    End With
End Sub