show date only if information entered in cell

  • Thread starter Thread starter digitalmuse
  • Start date Start date
D

digitalmuse

I want to have the current date show up in the first row say A whenever
someone enters information in a row - in other words if there is data in
cell C4 then the current date should show up in row A4
 
Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 3 Then
If Target.Value <> "" Then
Target.Offset(, -2).Value = Date
Else
Target.Offset(, -2).Value = ""
End If
End If
End Sub

Mike
 
Thank you so much works like a charm

Mike H said:
Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 3 Then
If Target.Value <> "" Then
Target.Offset(, -2).Value = Date
Else
Target.Offset(, -2).Value = ""
End If
End If
End Sub

Mike
Click to expand...
 
thanks for the responses and i guess i should have been clearer, the formula
cant depend on input to cell c4 only it needs to apply to any entry made in
column C
 
which i believe is why the formula now is not working it worked up until row 3
now anything entered in column c after row 3 does not result in a date entry
in colum a
 
Hi,

It's written to only work on column C (3)

If Target.Column = 3 Then

If you want to work on a different column change the 3 and the offset to 1
less than the column number. IF you want it to work on a number of columns
then that's a slight re-write

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:F")) Is Nothing Then
Application.EnableEvents = False
If Target.Value <> "" Then
Cells(Target.Row, 1).Value = Date
Else
Cells(Target.Row, 1).Value = ""
End If
Application.EnableEvents = True
End If
End Sub

Mike
 
It does work in a cell - im trying now to get it to work in all the cells in
that column(range)

thanks for the assist !
 
that works perfect in one cell what would i add or modify to have it apply to
an entire colum(or named range) i.e. range is named entryDate and covers
column A
 
Do you realize that the TODAY() function is volatile and will change when
you open the workbook tomorrow?

I would go with Mike's event code to add a static date in column A whenever
a cell in Column C is changed.


Gord Dibben MS Excel MVP
 
Back
Top