Date and Time

[RockStar75]

Hello all!

Is there way to automatically have the date updated in a cell every time a number in a different cell increases?

For example, say the date is in cell A1, and in cell B1 are sales in terms of units and let's say that when every a unit is sold, thereby increase the number in cell B1, you want the date to change to the current date.

Thank you very much in advance!

Chris

 

[OfficeUser]

Place this into the worksheet module for which you will be working with:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row = 1 And Target.Column = 2 Then
  Range("A1").Value = Date
End If
End Sub

Everytime the value changes in B1, the current date is placed into A1.

For Date & Time to show use:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row = 1 And Target.Column = 2 Then
  Range("A1").Value = Now
End If
End Sub

You may have to format A1 as Text for it to show correctly.

 

[RockStar75]

Thank you so much!!!! That's a great trick!!!!

Just one additional question, which is what if I have multiple cells were units are being updated (e.g. B1, B2, B3, etc.) and I need the corresponding cells in column A to show dates?

 

[Jonmo1]

Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MyRange As Range, c As Range

Set MyRange = Intersect(Target,Range("B:B"))
If MyRange Is Nothing Then Exit Sub
 
Application.EnableEvents = False
 
For Each c In MyRange
    c.Offset(0,-1).Value = Now
Next c
 
Application.EnableEvents = True
 
End Sub

 

[RockStar75]

Thank you so much!!!!!!!!!! : )

 

[RockStar75]

Hi jonmo1...there is a new issues that I have just encountered. The # of units in column B are increasing due to a sum calc of columns C through to F; when the number increases based on the sum calc the date does not update...it's only when the numbers are manually updated in column B. How would the code below adjust to reflect the sum calc.

Again, thanks! : )

Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MyRange As Range, c As Range

Set MyRange = Intersect(Target,Range("B:B"))
If MyRange Is Nothing Then Exit Sub
 
Application.EnableEvents = False
 
For Each c In MyRange
    c.Offset(0,-1).Value = Now
Next c
 
Application.EnableEvents = True
 
End Sub

 

[Jonmo1]

So you now have formulas in column B, and they are summing C to F, like
=SUM(C2:F2) ?


Change the logic just abit, we must monitor columns C to F then...
Because the change event does not trigger by formula..

Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MyRange As Range, c As Range
Set MyRange = Intersect(Target, Range("C:F"))
If MyRange Is Nothing Then Exit Sub
 
Application.EnableEvents = False
 
For Each c In MyRange
    Cells("A", c.Row).Value = Now
Next c
 
Application.EnableEvents = True
 
End Sub

 

[RockStar75]

Hmmmm...something has gone wrong. When I copied and pasted the code and then did a sum calc in cell B1 and then keyed in values into C1 the following error message popped up:

Run-time error '13':

Type mismatch

And it's highlighting the following row:

For Each c In MyRange
Cells("A", c.Row).Value = Now

What have I done wrong?

 

[Jonmo1]

Oh crap...strike that, reverse it..
Cells("A", c.Row).Value = Now
should be
Cells(c.Row, "A").Value = Now

 

[RockStar75]

1,000 thank you's!!! : )