boucle calcul d'heure en vba suivant condition

Private Sub CommandButton1_Click()
  Dim res As Single
  res = Application.WorksheetFunction.SumIf(Range("c12:c50"), "Garde", Range("f12:f50"))
  With Range("a1")
    .Value = res
    .NumberFormat = "[hh]:mm"
  End With
  TextBox1 = Range("A1").Text
End Sub

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim plage, cel As Range
Application.EnableEvents = False
Set plage = Range("c12:c31")
For Each cel In plage
If cel.Offset(0, 0).Value = Range("g2") Then
cel.Offset(0, 0).Font.Color = Range("g2").Font.Color
cel.Offset(0, 1).Interior.Color = vbYellow
cel.Offset(0, 2).Interior.Color = vbYellow
UserForm1.Show
Else
cel.Offset(0, 0).Font.Color = xlDefaut
cel.Offset(0, 1).Interior.Color = xlNone
cel.Offset(0, 2).Interior.Color = xlNone
End If
Next cel
Application.EnableEvents = True
End Sub


Option Explicit
Dim x As Date
Private Sub CommandButton1_Click()

With Sheets("Liste")
x = Application.SumIf(.Range("c12:c31"), .Range("g2"), .Range("f12:f31"))
TextBox1 = Int(CDec(24 * x)) & ":" & Format(Minute(x), "00")
End With
End Sub