ㄹㅇ 마지

[김진태]

Sub 게이트시간계산()

Dim ws As Worksheet
Set ws = ActiveSheet

Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Dim r As Long
Dim c As Long

Dim t1 As Date
Dim t2 As Date

Dim countTime As Integer
Dim firstCol As Long
Dim secondCol As Long
Dim firstYellowCol As Long

Dim txt As String

For r = 4 To lastRow

countTime = 0
firstYellowCol = 0

' 시간 찾기
For c = 2 To 7

txt = ws.Cells(r, c).Text

If txt <> "" Then

If IsDate(Left(txt, 5)) Then

countTime = countTime + 1

If countTime = 1 Then
t1 = TimeVal‎ue(Left(txt, 5))
firstCol = c
ElseIf countTime = 2 Then
t2 = TimeVal‎ue(Left(txt, 5))
secondCol = c
End If

End If

End If

Next c

' 시간 2개 있으면 계산
If countTime >= 2 Then

' 첫 노란칸 찾기
For c = firstCol + 1 To secondCol - 1

If ws.Cells(r, c).Interior.ColorIndex = 6 Then

firstYellowCol = c
Exit For

End If

Next c

' 시간 입력
If firstYellowCol <> 0 Then

ws.Cells(r, firstYellowCol).Value = t2 - t1
ws.Cells(r, firstYellowCol).NumberFormat = "[h]:mm"

End If

End If

Next r

MsgBox "완료"

End Sub