Trying To Automatically Fill Desired Values Multiple Times

[HelpShaneExcel]

Hi Friends. Am new here.

So here is what I'm trying to do. At work for our spreadsheets, we have to enter certain information. For these particular spreadsheets we log whenever somebody checks out a fuel key, and brings it back with their gallons.

The columns are set up as follows: (In this order and appearing on the spreadsheet from left to right and under columns respectively A,B,C,D,E,F,G,H,I)

Date | Name | Vehicle | Key | Time Out | Initials | Time In | Gallons | Initials

My boss asked if it would be possible to have it so that whenever we enter someone's name under the Name column, the Date and Time Out (time they checked out the fuel lock keys) would automatically fill with date and time respectively.

I found a code from searching online, and with a bit of tweeking, I made it so that it works as desired. It is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim B As Range, Inte As Range, r As Range

  Set B = Range("B:B")
  Set Inte = Intersect(B, Target)
  If Inte Is Nothing Then Exit Sub
  Application.EnableEvents = False
  For Each r In Inte
  r.Offset(0, -1).Value = Date
  r.Offset(0, 3).Value = Time
  Next r
  Application.EnableEvents = True

End Sub

Now I have searched and searched and searched, and have tried figuring out every possible thing I can think of in my own attempts to write a code that makes it so that when somebody brings the fuel key back and tells us the amount of gallons they used, upon entering that amount in its cell (H), the 'Time In' (G) also fills in with time, but I have had no luck. Again I've tried EVERYTHING I can think of.

Does anybody know what I need to add to this existing code so that not only the first time entering the person's name auto-fills the date and time out cells, but so that upon their return and entering the amount of gallons they checked out the 'time in' cell will also fill automatically with time?

Thanks so much

 

[Hui]

Shane

Firstly, Welcome to the Chandoo.org Forums

Private Sub Worksheet_Change(ByVal Target As Range)
Dim B As Range, Inte As Range, r As Range

Application.EnableEvents = False

Select Case Target.Column
Case 2
  Set B = Range("B:B")
  Set Inte = Intersect(B, Target)
  For Each r In Inte
  r.Offset(0, -1).Value = Date
  r.Offset(0, 3).Value = Time
  Next r

Case 8
  Set B = Range("H:H")
  Set Inte = Intersect(B, Target)
  For Each r In Inte
  r.Offset(0, -1).Value = Time
  Next r

Case Else
  Application.EnableEvents = True
  Exit Sub
End Select

Application.EnableEvents = True

End Sub

 

[HelpShaneExcel]

HUI!!! I FREAKIN' LOVE YOU MAN!!! LOL

That was so nice of you to actually write it out for me as I probably would not have understood any instructions anyways. But after several weeks of searching and trying to figure it out on my own your code worked in our sheets beautifully! Thank you so much my friend you are awesome! :D