MS Excel Time in specific cell when A1 contains data

[5pds]

Aim is when a vehicle has been assigned to collection, time of entry appears in Load cell coresponding with vehicle eg.e vehicle 1 'A1' = load 1 'B1'. I can get the exact time to appear in the cell currently but when I try to assign a second truck, the load 2 'B2' cell AND the load 1 'B1'. Im sure that there is an easy way to do this but i just cant figure it out. Can someone please help me out. Can i disable the cell that is updating?

I have the spreadseet and if anyone thinks that they can help i can sent it through

My knowledge of these things are limited so excuse the naivety

 

[VoG]

Hello and welcome to MrExcel.

Are you saying that when something is entered in column A then a timestamp should appear in column B? If so then right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Application.EnableEvents = False
    Target.Offset(, 1) = Now
    Application.EnableEvents = True
End If
End Sub

 

[5pds]

Hi there, thanks for the prompt reply. This may be what i am looking at. would it be possible that i could send through the worksheet and you can have a look? It is very difficult to explain????

You could mail me on Removed if possible.

Thanks again, really appreciate this.

Paul

 

[lenze]

Hi and welcome to the board!!
VoG's macro should do that. Basically, if a Value is entered in Column "A", the current Date and Time will be entered in column "B". You can not use a formula such as NOW() or TODAY() because that change when the WS is recalculated

lenze

 

[5pds]

Thanks for the response. So if i copy and paste this into the VBA screen it will work? Will i have to assign this to a macro button becuase that would not be ideal for what i need it for.

Thanks

Paul

 

[lenze]

Thanks for the response. So if i copy and paste this into the VBA screen it will work? Will i have to assign this to a macro button becuase that would not be ideal for what i need it for.

Thanks

Paul

No button or shortcut required!! This is an Event Procedure called WorkSheet_Change. It is set for Column "A" (1 in the code). It runs automatically when a cell in column A is changed.
lenze

 

[5pds]

Right,

I am fairly new to this and struggling to get my head round this.

The info that i want to enter is in G7 and i want the time to appear in P7 as a result of entering the code?

Thanks

Paul

 

[lenze]

Just G7, or any Cell from G7 down? For just G7, use

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$G$7" Then Exit Sub
Range("$P$7") = Now
End If

For all of Column "G", use

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 7 Then Exit Sub
Cells(Target.Row , "P") = Now
End Sub

HTH
lenze

 

[5pds]

Just G7, or any Cell from G7 down? For just G7, use

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$G$7" Then Exit Sub
Range("$P$7") = Now
End If

For all of Column "G", use

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 7 Then Exit Sub
Cells(Target.Row , "P") = Now
End Sub

HTH
lenze

Thanks for this, going to try this and sign off for the night. I will however be looking to use Columns G,H,I,J and K also and from 7 to 154 (ranges)

Ideas?

 

[lenze]

OK. I know you're new to the board, so don't take this wrong. It is, however, usually best to describe the whole problem up front in the original post.(ie What you want to do) It takes more up-front work on your part, but you will tend to get faster and better results.
Anyway. Can you be more specific as to your needs. What happens when a cell in Column "G" is changed? Column "H"?, Etc. The code is simple, but you need to provide as much details as possible.
lenze