Option Explicit
Sub test()
Dim dl As Integer, c As Range, i As Integer
With Sheets("Report")
dl = .Range("Q" & Rows.Count).End(xlUp).Row
For Each c In .Range("Q2:Q" & dl)
If c.Value = .Range("S1") Then
If c.Offset(, -4).Value = "Mauvais" Then
i = i + 1
End If
End If
Next
MsgBox "Pour l'" & .Range("S1") & " il y a " & i & " mauvais"
End With
End Sub