Sub MsgControle()
Dim tablo, msg1$, msg2$
With Feuil2
tablo = .Range("B4:G23")
For i = 1 To UBound(tablo, 1)
If IsDate(tablo(i, 6)) Then
If VBA.Date > CDate(tablo(i, 6)) Then
msg1 = msg1 & tablo(i, 1) & " expiré depuis " & VBA.Date - tablo(i, 6) & " jours" & vbCr
ElseIf VBA.Date > CDate(tablo(i, 6)) - 10 And VBA.Date <= CDate(tablo(i, 6)) Then
msg1 = msg1 & tablo(i, 1) & " expire dans " & CDate(tablo(i, 6)) - VBA.Date & " jours" & vbCr
End If
End If
If IsDate(tablo(i, 5)) Then
If VBA.Date > CDate(tablo(i, 5)) Then
msg2 = msg2 & tablo(i, 1) & " expiré depuis " & VBA.Date - tablo(i, 5) & " jours" & vbCr
ElseIf VBA.Date > CDate(tablo(i, 5)) - 10 And VBA.Date <= CDate(tablo(i, 5)) Then
msg2 = msg2 & tablo(i, 1) & " expire dans " & CDate(tablo(i, 5)) - VBA.Date & " jours" & vbCr
End If
End If
Next
End With
MsgBox "Contrôle technique:" & vbCr & msg1 & vbCr & "Contrôle pollution:" & vbCrLf & msg2
End Sub