Excel / VBA - Detecting changes in cell

July 2017


When using the VBA, detecting the changes in cells is very easy. It can be done through the Event Change feature on the Excel worksheet. However, there is no method to determine the contents of the cell with the Event Change feature. Also, the event change just takes the active cell into consideration and it cannot check for changes once the cell is left. Although the cell is no longer selected, It is not difficult to detect the changed cell content with the VBA. It is simple to manage a VBA event for a cell change. With the VBA, detecting the changes in the cell adds flexibility and features to Office software.


The Event Change feature of a sheet detects changes in the active cell but it gives no information about the content. The example given below will help you find out if a cell still works after the contents have been modified. Copy and paste the code, adding in the action you want to be taken when a cell is changed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)           
Static AncAdress As String, AncCell As Variant           
    If AncAdress <> "" Then 'for first initialization.           
        If AncCell <> Range(AncAdress) Then           
            'The cell that you just left has been changed.            
            'Put action to be taken.           
            Stop           
        End If           
    End If           
    AncAdress = Target.Address           
    AncCell = Target.Value2           
End Sub

Related


Published by netty5. Latest update on September 12, 2012 at 06:41 AM by deri58.
This document, titled "Excel / VBA - Detecting changes in cell," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).