Static date in selected column as formula change value

Question

I would like to accomplish this through excel macro:

In my workbook Sheet1, values are entered in Range (A2:H20) through formulae.

I also have Range (J2:P20) that will keep track of change of values in Range (A2:H20) with a Date stamp in this format dd-mmm-yyyy.

Now I want to do this:

if a value is changed in A2 the date stamp should reflect in J2
if a value is changed in A3 the date stamp should reflect in J3
if a value is changed in B2 the date stamp should reflect in K2
if a value is changed in B3 the date stamp should reflect in K3

And that goes the same with other cells within those ranges.

The date stamp should replace any existing date (if any one exist) in its respective cells. If any cell in the Range (A2:H20) is =0 or empty there should be no date stamp in the corresponding cell.

hydrox467 hydrox467 · Accepted Answer · 2013-05-30T05:32:39

Here is a example worked up for just cell A2/J2. You'll have to extend the code if you want to use it for the entire range. The code below goes in the "Worksheet_Change" Procedure of your sheet. Part of the example is from Microsoft

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
'Code Example from Microsoft.com
'http://support.microsoft.com/kb/213612


' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A2:H20")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
       Is Nothing Then

    ' Display a message when one of the designated cells has been
    ' changed.
    ' Place your code here.
    'MsgBox "Cell " & Target.Address & " has changed."  <--- Original Microsoft Example

    'Code to Check Value/Status of A2
    If Target.Address = "$A$2" Then
        If Range("A2").Value <> "" Then
        Range("J2").Value = Now()
        Else
        Range("J2").Value = ""
        End If
    End If

End If
End Sub

Static date in selected column as formula change value

1 Answers