I have a code here that can highlight cell that has changed.
This code of mine will also delete the highlighted cells if the cell is on its original data.
My problem here is when I copy/paste only the first cell that was copied will be highlighted.
What I want to do here is highlight all cells that has changed not just the first cell.
I used collection in my code to store the old values in the cells
This is my code:
Dim oval As New Collection
Dim cell As Range
Dim newCell As Range
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each cell In Target
If Not cell.Locked And cell.Interior.Color = RGB(255, 255, 255) Then
If Exists(oval, cell.Address) Then
oval.Add cell.Value, cell.Address
Private Sub Worksheet_Change(ByVal Target As Range)
For Each newCell In Target
If Exists(oval, newCell.Address) Then
If newCell.Value <> oval(newCell.Address) Then
newCell.Interior.Color = RGB(255, 255, 0)
newCell.Interior.ColorIndex = xlNone
Function Exists(coll As Collection, key As String) As Boolean
On Error GoTo EH
Exists = True