Macro needed to log date and time

Savuti

Board Regular
Joined
Apr 30, 2008
Messages
156
Hello, the code below is not complete. I need to have the date and time show up in columns G and H respectively when the word Sunday shows up in the corresponding B column. I need the same for J and H if the word Monday shows up in the corresponding B column.

Thanks for the help

1st -
Code:
Cells(Target.Row, "G").Value = Date
          Cells(Target.Row, "H").Value = Time

2nd -
Code:
Cells(Target.Row, "J").Value = Date
          Cells(Target.Row, "K").Value = Time
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
You look pretty comfortable with your code but try this too...

Code:
[/FONT]
[FONT=Courier New]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lr As Long
lr = Range("B" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=Courier New]If Target.Count > 1 Then Exit Sub[/FONT]
[FONT=Courier New]For Each Target In Range("B1:B" & lr)
 If Target.Value = "Sunday" Then Cells(Target.Row, "G").Value = Now
 If Target.Value = "Monday" Then Cells(Target.Row, "J").Value = Now
Next Target
End Sub
 

Savuti

Board Regular
Joined
Apr 30, 2008
Messages
156
pedie, this does work, but how would I get the date and time to show up in cell G and H respectively if Sunday is in "B" and the date and time to show in J and K if Monday is in "B"

I hope this makes sense and I thank you for your time.
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Try

Rich (BB code):
private sub worksheet_selectionchange(byval target as range)
dim lr as long
lr = range("b" & rows.count).end(xlup).row
if target.count > 1 then exit sub
for each target in range("b1:b" & lr)
if target.value = "sunday" then cells(target.row, "g").value = date and cells(target.row, "h").value = time
if target.value = "monday" then cells(target.row, "j").value = date and cells(target.row, "k").value = time
next target
end sub
 

Savuti

Board Regular
Joined
Apr 30, 2008
Messages
156

ADVERTISEMENT

pedie, strangely the time does not show up and the date shows as 00/01/1900
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
This works for me. Try checking the format part too.
else we can use format(range,"yourformat")..



Code:
[/FONT]
[FONT=Courier New]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lr As Long
lr = Range("B" & Rows.Count).End(xlUp).Row
If Target.Count > 1 Then Exit Sub
For Each Target In Range("B1:B" & lr)
 If Target.Value = "Sunday" Then
 Cells(Target.Row, "G").Value = Date
 Cells(Target.Row, "H").Value = Time
 ElseIf Target.Value = "Monday" Then
 Cells(Target.Row, "J").Value = Date
 Cells(Target.Row, "K").Value = Time
 Else
 End If
Next Target
End Sub
 

Savuti

Board Regular
Joined
Apr 30, 2008
Messages
156
pedie, everything goes to the right place - date and time, every time though I make an entry down the rows in B column, all the previously entered times in H and K update to the time of the most recent entry. I am wondering if there is something else in my spreadsheet that could be out of wack that is causing this. You have been most gracious and patient, perhaps it is something that you are aware of and have a fix, otherwise I will leave the time stamp out and just use the date.
Thanks
once again.
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Will check this again later, maybe someone else too can look into this..
got some work at the moment
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top