How to Adjust Column Target

[ohsoquiet]

Until today I had never heard of VBA code so I apologize in advance for asking what is probably an obvious question. I've been asked to find a way to enter times into Excel that do not require the typist to enter a colon or using military time. For instance, one could type "443 p" and Excel would turn that into "4:43 PM" or I could enter "1231 a" and Excel would turn it into 12:31 AM. I have found a code that does this, but despite hours of searching I can not figure out how to make the code work on more than one column. Here is the code:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range, dblTime As Double
On Error GoTo ExitPoint
If Intersect(Target, Columns(4)) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rngCell In Intersect(Target, Columns(4)).Cells
If rngCell.Value <> "" Then
With rngCell
dblTime = TimeValue(Format(Val(.Value), "00\:00"))
dblTime = TimeSerial(Hour(dblTime) Mod 12, Minute(dblTime), 0)
dblTime = dblTime + IIf(UCase(Right(rngCell.Value, 1)) = "P", 0.5, 0)
.NumberFormat = "h:mm AM/PM"
.Value = dblTime
End With
End If
Next rngCell
ExitPoint:
Application.EnableEvents = True
End Sub
I've figured out that the number "4" after "Target, Columns" refers to Column D. So I can obviously change that to "3" to make it refer to Column C, but what if I want it to refer to both columns C and D? Or Column A and F? Or A, C, and F? Any help would be greatly appreciated!

[Simon Lloyd]

Do you want it to work on all columns or do you have a specific range?

[ohsoquiet]

Do you want it to work on all columns or do you have a specific range?

Thanks for your reply. The problem is that I don't know which columns or even how many columns need the code. What I'm hoping is that someone can tell me the theory behind applying this to more than one column (possibly adjacent but possibly not) and when I find out which columns are being used, I can just plug them into the code.

I hope that makes sense.

[Simon Lloyd]

You can change these lines
If Intersect(Target, Columns(4)) Is Nothing Then Exit SubFor Each rngCell In Intersect(Target, Columns(4)).Cells
For
If Intersect(Target, Range("B:H")) Is Nothing Then Exit SubFor Each rngCell In Intersect(Target, Range("B:H")).CellsNot knowing which columns will cause you a massive headache, you need some criteria to narrow it down, if you just want it to wok anywhere then you can use this:Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range, dblTime As Double
On Error GoTo ExitPoint
Application.EnableEvents = False
For Each rngCell In Target
If rngCell.Value <> "" Then
With rngCell
dblTime = TimeValue(Format(Val(.Value), "00\:00"))
dblTime = TimeSerial(Hour(dblTime) Mod 12, Minute(dblTime), 0)
dblTime = dblTime + IIf(UCase(Right(rngCell.Value, 1)) = "P", 0.5, 0)
.NumberFormat = "h:mm AM/PM"
.Value = dblTime
End With
End If
Next rngCell
ExitPoint:
Application.EnableEvents = True
End Sub