Converting Numeral to Time equivalent
Hi Team
Was hoping someone was able to point me in the right direction please:
I am trying to speed up data entry by just entering a number and have in auto-convert to time, eg 515 = 5:15 or 1415 = 14:15, reducing keystrokes will significantly speed up this process in Columns( 10 & 11 ).
I tried inserting it into my Worksheet_Change event, but it will not fire ( although it fires if I run the sub on it's own ).
I have this so far, so if anyone can point me in the right direction, I will be super-grateful.
As always, much appreciation
TIA
Mark.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C3:C43")) Is Nothing Then
If Target.Cells.Count 1 Then Exit Sub
Select Case Target.Column
Case 3
If Target.Value = "YARD" Or Target.Value = "C0654" Then
Target.Offset(0, 7).Select
Else
Target.Offset(0, 1).Select
End If
Case 8
Target.Offset(0, 2).Select
Case 10
NumberToTime
Target.Offset(0, 1).Select
Case 11
NumberToTime
End Select
End If
End Sub
Private Sub NumberToTime()
Dim rCell As Range
Dim iHours As Integer
Dim iMins As Integer
For Each rCell In Selection
If IsNumeric(rCell.Value) And Len(rCell.Value) 0 Then
iHours = rCell.Value \ 100
iMins = rCell.Value Mod 100
rCell.Value = (iHours + iMins / 60) / 24
rCell.NumberFormat = "[h]:mm"
End If
Next
End Sub
|