How can I highlight all cells that is changed?

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.Remove cell.Address
            End If
            oval.Add cell.Value, cell.Address
        End If
        Next cell
    End Sub
    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)
                Else
                    newCell.Interior.ColorIndex = xlNone
                End If
            End If
            Next newCell
        End Sub
        Function Exists(coll As Collection, key As String) As Boolean
            On Error GoTo EH
            coll.Item key
            Exists = True
EH:
        End Function

Open in new window

NewBieSteffieInternAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
Here's a version that takes row and column, deletion and insertion into consideration. There are two things to note:
  • The Workbook_Open event refers to Sheet1 so if that name is changed the code in Workbook_Open() will need to be change, and
  • The code is complex, so test it to your satisfaction.
29077502a.xlsm
0
 
Martin LissOlder than dirtCommented:
Try this workbook that uses a different approach. It uses a sheet called 'OldData' to store the old values from Sheet1 for comparison purposes. It's unclear to me when you want to remove the yellow highlighting so please explain further if you need my help in doing that.

BTW both your method and mine (as is) will not work properly if rows and/or columns where deleted and/or added. Let me know if you want to consider those situations.
29077502.xlsm
0
 
NewBieSteffieInternAuthor Commented:
Hi Martin Liss,
I need to remove the yellow highlighting when the data in the cell is back to its very first original data.
We will highlight the cell if the cell data is changed but if it is changed and then if it back to the very first data the highlight will be gone.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NewBieSteffieInternAuthor Commented:
Thank you in advance
0
 
Martin LissOlder than dirtCommented:
Are you saying that if for example A1 = 1 and it's change to 2 (which will turn it yellow) and then it's changed to 2 and then it's changed to 3, that if it is changed back to 1 that you don't want it to be yellow?
0
 
NewBieSteffieInternAuthor Commented:
Yes Sir Martin Liss :)
0
 
Martin LissOlder than dirtCommented:
If you want to consider the cases where rows and/or columns where deleted and/or added then that would be very complex because the original data would also need to be changed and I would not want to make the coding changes, so please let me know if you do want to consider those cases.
0
 
Martin LissOlder than dirtCommented:
By the way, the sheet could be protected so that those kind of changes are not allowed.
0
 
NewBieSteffieInternAuthor Commented:
Thank you :)
0
 
NewBieSteffieInternAuthor Commented:
@Martin Liss I will take note of your suggestion I will modify the code that you gave and paste it here if I already found the answers that suits my requirement.
0
 
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
 
NewBieSteffieInternAuthor Commented:
Noted Martin Liss,
Thank you!
It is very helpful to me since I am in the process of studying and learning VBA
0
 
Martin LissOlder than dirtCommented:
If you do find them useful then a click of the thumbs up button would be appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.