Compare new cell content with old

Chris15

New Member
Joined
Jan 3, 2019
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
When the user selects a cell within an already populated portion of a list, the original contents are copied into column B of the same row. I then need a pause or something, so if the user types something different in the cell I can capture the new contents and compare to the old, if different then the date/time stamp (will be put in column F) will be updated otherwise left untouched.
Have marked where I think the pause should go in the code below with " 'User changes cell "
Can anyone help please?
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ListsRnge As Range
    Dim OldColCCnt As Integer
    Dim OldColECnt As Integer
    Dim OldColGCnt As Integer
    Dim ChngCol As Integer
    Dim ChngRow As Integer
    'Dim OldContent As String
    Dim NewContent As String
    
    'Specify ListsRnge: columns C&D = Chill, columns E&F = Focus, columns G&H = Sleep
    Set ListsRnge = Range("C9:H500")
          
    'Check if changed cell is in one of the above required columns, if not don't run routine
    If Intersect(Target, ListsRnge) Is Nothing Then Exit Sub
    
    'Switch off events
    Application.EnableEvents = False
    
    'Count number of rows already populated in each of the columns C, E and G
    OldColCCnt = Cells(Rows.Count, "C").End(xlUp).Row
    OldColECnt = Cells(Rows.Count, "E").End(xlUp).Row
    OldColGCnt = Cells(Rows.Count, "G").End(xlUp).Row
        
    'Determine which of the Updated date/times should change
    ChngCol = Target.Column
    ChngRow = Target.Row
    If ChngCol = 3 And Target.Row <= OldColCCnt Then
        'Put old cell content into column B, same row
        Cells(ChngRow, 2).Value = Target.Value
        'User changes cell
        NewContent = Target.Value
        

    End If
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,519
Office Version
  1. 2013
Platform
  1. Windows
Since the Worksheet_SelectionChange event is triggets the macro to run immediately upon selection of the cell, the user would not have made any change in the target cell at run time. You might want to consider changing the macro to use a Worksheet_Change event which would allow the user to make a change before running the macro.
 

Chris15

New Member
Joined
Jan 3, 2019
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Unfortunately that doesn't work as it only copies the cell contents from C into B after you have made the change. Can I have a Worksheet_Activate event where I copy the whole original column C into column B then go into the Worksheet_SelectionChange event to see if the 2 values match? How do I tell it to move from the Activate event to the SelectionChange event, would I need to declare all the variables in both events?
 

Chris15

New Member
Joined
Jan 3, 2019
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Worksheet_Activate doesn't seem to work when there is only 1 sheet in the workbook so have put the following in the Workbook_Open, which does work, however I have to keep closing & then re-opening the file to make it work so I can check out each change I make to the Worksheet_SelectionChange event. Is there a way round this?
Am only starting with column C, 'coz if that works I can make it work for the other columns.
Sorry if this is a stupid question but I am learning as I go

VBA Code:
Private Sub Workbook_Open()
    Application.EnableEvents = True
    Sheet1.Activate
    ActiveSheet.Select
    Dim OldColCCnt As Integer
    Dim OldColHCnt As Integer
    
    'Count number of rows already populated in each of the columns C and H for copying
    OldColCCnt = Cells(Rows.Count, "C").End(xlUp).Row
    OldColHCnt = Cells(Rows.Count, "H").End(xlUp).Row
    
    'Copy old contents for later comparison
    'Range(Cells(9, "C"), Cells(OldColCCnt, "C")).Select
    Range(Cells(9, "C"), Cells(OldColCCnt, "C")).Copy
    Range("B9").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("F1").Select
End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,519
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

I had something like this in mind.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim newDat As String, oldDat As String
    If Not Intersect(Target, Columns("A")) Is Nothing Then
        newDat = Target.Value
        Application.Undo
        oldDat = Target.Value
        Target = newDat
        If newDat <> oldDat Then
            Target.Offset(, 5) = Now
        End If
    End If
Application.EnableEvents = True
End Sub
 

Chris15

New Member
Joined
Jan 3, 2019
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Changed Columns("A") to read Columns("C") and .Offset(,5) to .Offset(,8) but as soon as I click anywhere in column C (eg C1) cell K1 gets updated. I only want the macro to fire if I do something between C9 & H500, so Worksheet_Change won't work for me. Is there a way to trigger the Workbook_Open event without closing & re-opening everytime?
 

Chris15

New Member
Joined
Jan 3, 2019
Messages
7
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Sorry about this JLGWhiz, have got bits of code all over the place and got myself thoroughly confused!
Putting this in the Worksheet_Change the first part works alright (have still got the copy column C cells into column B in the Workbook_Open), in that if I change the contents of a cell eg change C5 then B5 and K5 changes correctly. However it's falling over on the rest. There are currently 22 lines in column C, if I type something in C25 (until I've worked out how to get a message box) it should be putting the word "Wrong" in column J, it isn't it's putting date/time in K, if I type something in C23 (which is adding to the current list) it should put the date/time in J but it's putting it in K.
I would like to be able to change the current name of C and it be recorded in K, if I add a new line to the list in column C this will update J.
Once I've got column C working correctly will expand the copy/paste in the Workbook_Open event to copy C9:H? and put it in columns R to Y, am only using columns C and B for the moment so I can see, easily, what changes are being made.
Does this make sense, would it help if I emailed you the workbook?




VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ListsRnge As Range
    Dim OldColCCnt As Integer
    Dim OldColECnt As Integer
    Dim OldColGCnt As Integer
    Dim ChngCol As Integer
    Dim ChngRow As Integer
    ''Dim OldContent As String
    ''Dim NewContent As String
    
    'Specify ListsRnge: columns C&D = Chill, columns E&F = Focus, columns G&H = Sleep
    Set ListsRnge = Range("C9:H500")
          
    'Check if changed cell is in one of the above required columns, if not don't run routine
    If Intersect(Target, ListsRnge) Is Nothing Then Exit Sub
      
    'Switch off events
    Application.EnableEvents = False
    
    'Count number of rows already populated in columns C, E and G
    'OldColCCnt = Cells(Rows.Count, "C").End(xlUp).Row
    'OldColECnt = Cells(Rows.Count, "E").End(xlUp).Row
    'OldColGCnt = Cells(Rows.Count, "G").End(xlUp).Row
    
    'Determine which of the Added/Updated date/times should change
    ChngCol = Target.Column
    ChngRow = Target.Row
    'Adding/Updating Chill list
    If ChngCol = 3 Then
       'Count number of rows already populated in column C
        OldColCCnt = Cells(Rows.Count, "C").End(xlUp).Row
        If Target.Row <= OldColCCnt Then
            'Updating Chill list, user changes name, compare new value to old (previously already copied into column B when workbook was opened
            '(in ThisWorkbook,Workbook_Open)
            If Cells(ChngRow, "C").Value <> Cells(ChngRow, "B").Value Then
                'Change Updated date/time in column K
                Cells(ChngRow, "K").Value = Now
                'Put new contents of row into column B
                Cells(ChngRow, "B").Value = Cells(ChngRow, "C").Value
            End If
        End If
    End If
    If ChngCol = 3 Then
        'Count number of rows already populated in column C
        OldColCCnt = Cells(Rows.Count, "C").End(xlUp).Row
        If Target.Row >= (OldColCCnt + 1) Then
            'Display message if leaving blank lines between entries
            Cells(ChngRow, "J").Value = "Wrong"
        End If
    End If
    If ChngCol = 3 Then
        'Count number of rows already populated in column C
        OldColCCnt = Cells(Rows.Count, "C").End(xlUp).Row
        If Target.Row = (OldColCCnt + 1) Then
            'Adding new line onto Chill list
            If Cells(ChngRow, "C").Value <> "" Then
                'Put Added date/time in column J
                Cells(ChngRow, "J").Value = Now
                'Put new contents of row into column B
                Cells(ChngRow, "B").Value = Cells(ChngRow, "C").Value
            End If
        End If
    End If
        
    '*************
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,519
Office Version
  1. 2013
Platform
  1. Windows
I don't think I can offer much more on this so I am going to break off the thread. Good luck.
Regards, JLG
 

Chris15

New Member
Joined
Jan 3, 2019
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Don't know if you'll get this JLGWhiz but I want to say Thanks. Although I couldn't get your suggestion to work for me in the way that I want this spreadsheet to update, you did point me in the right direction. Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,439
Members
410,684
Latest member
LakTik
Top