Excel display time NOW in cells automatically on sheet change

Question

In my A1,A2,...,A10 cells i have this code

=IF(B1>0,NOW(),0)

So if B1>0 A1 equals to Date time now and all this for B2 and A2 B3 and A3 and so on.

But the problem is that if i change for example B4 all cells from A1 to An automatically change to the date of A4 actually if i do something on this sheet A1 ... An automatically change to the current Time NOW

Possible duplicate of Automatically update cell in Excel with current timestamp when another cell is changed — Burgi, Jan 08 '16 at 11:32
The normal behavior of NOW() is to update any time the sheet is recalculated. Are you asking how to have it update only if a specific cell changes? It isn't clear whether you're describing what you want to happen, or the problem of what does happen and you want to change it. — fixer1234, Jan 08 '16 at 19:35

score 0 · Answer 1 · answered Jan 08 '16 at 12:54

Try this short Event Macro:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim B As Range, I As Range, cell As Range
   Set B = Range("B1:B10")
   Set I = Intersect(Target, B)
   If I Is Nothing Then Exit Sub

   Application.EnableEvents = False
      For Each cell In I
         If cell.Value > 0 Then
            cell.Offset(0, -1).Value = Now()
         Else
            cell.Offset(0, -1).Value = 0
         End If
      Next cell
   Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

right-click the tab name near the bottom of the Excel window
select View Code - this brings up a VBE window
paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

bring up the VBE windows as above
clear the code out
close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Excel display time NOW in cells automatically on sheet change

1 Answers1