CODEWITHSUNDEEP
excelvbahighlight
Chs
Chs

Reputation: 129

How to highlight only the last edited row?

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.

Upvotes: 3

Views: 201

Answers (2)

JvdV
JvdV

Reputation: 75840

Here is some code that could work for you:

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

'Removing old conditional formatting if it exists already
Conditions = ActiveSheet.Cells.FormatConditions.Count
For i = 1 to Conditions
    If ActiveSheet.Cells.FormatConditions(i).Type = 2 Then
        If ActiveSheet.Cells.FormatConditions(i).Formula1 = "=1" Then ActiveSheet.Cells.FormatConditions(i).Delete
    End If
Next i

'Adding new conditional formatting rule to the edited range
Target.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:="=1"
Target.EntireRow.FormatConditions(1).Interior.ColorIndex = 6

End Sub

Currently it will highlight all the last edited rows in all sheets. Not sure if this is what you wanted.

  • It will keep colored cells in your sheet intact when a new range has been changed.
  • It will keep other conditional formatting rules intact.
  • It highlights the last edited range, even if the range has been cleared!

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149287

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.

Upvotes: 3

Related Questions