Auto date entry?

S

sigfreund

Can I have the time entered into cell a1 automatically by entering a
value into b1 or c1, if so how can I have this continue down the entire
colum. I am stuck in a hospital bed and need to figure out an easy way
to track some fluid ins and outs and keep a running total. Thanks
 
N

nastech

not sure if this is what you want; this is code for enter anything in a
specified cell in "1 line", get date in different cell same line. this
example has a couple of items added: uses range, exit if less than certain
row (header), exit lines not to be figured (that have a "." in first cell in
line); Destination Cell A, for changes made in
B or C.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Target.Row < 91 Then Exit Sub
If Me.Cells(.Row, "A").Value = "." Then Exit Sub
If Not Intersect(Me.Range("B:C"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, "A")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub
 
N

nastech

2 date example below: date format for view I chose was dd, you can use any
combination of: yymmdd hh:mm:ss

enter code by Right-Click worksheet tab at bottom, View Code, & paste to
worksheet window provided, CLICK SAVE.

anytime you move columns around, you will have to modify this code to suite.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Target.Row < 90 Then Exit Sub
If Me.Cells(.Row, "A").Value = "." Then Exit Sub
If Not Intersect(Me.Range("B:C"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, "A")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
If Not Intersect(Me.Range("E:F"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, "D")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub
 
B

Bob Phillips

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error GoTo ws_exit
Application.EnableEvents = False
With Target
If .Column = 1 Then
.Offset(0, 1).Value = Time
.Offset(0, 1).NumberFormat = "hh:mm:ss"
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
N

nastech

thought you might be interested, the conditional formatting I use for 3
different colors are: (A4: x or y, are over-rides; a5 6 7 are x days out: 30
20 10..; purple, lite purple, yellow; lime green for column fades date to
back ground if ok)


=IF(OR(A9="",$A$4="x"),"",(TODAY()+1)>(A9+A$5))
=IF(OR(A9="",$A$4="x"),"",(TODAY()+1)>(A9+A$6))
=IF(OR(AND(J9="",AT9=""),$A$4="x",$A$4="z"),"",OR(A9="",(TODAY()+1)>(A9+A$7)))
 
Top