MsgBox on passed date, multiple rows = multiple boxes, want 1 box all msgs.

[SlootsJ]

Private Sub Workbook_open()
   Dim r As Long
   Dim Msg As String
   With Sheets("HTL")
       For r = 11 To 69
           If .Range("C" & r).Value > 0 And .Range("W9").Value < .Range("N" & r).Value Then
               MsgBox .Range("C" & r).Value & " heeft nog 1 maand tot uitstroom."
           End If
       Next r
       End With
End Sub

I have created a MsgBox code wich works perfectly, except if 40 dates are between date range, it pops 40 seperate messageboxes, I want all rows wich meets the criterea in 1 box.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Workbook_open()
   Dim r As Long
   Dim Msg As String
   With Sheets("HTL")
       For r = 11 To 69
           If .Range("C" & r).Value > 0 And .Range("W9").Value < .Range("N" & r).Value Then
               Msg = .Range("C" & r).Value & vbLf
           End If
       Next r
       If Msg <> "" Then MsgBox Msg & " heeft nog 1 maand tot uitstroom."
       End With
End Sub

 

[SlootsJ]

Private Sub Workbook_open()
Dim r As Long
   Dim Msg As String
   With Sheets("HTL")
       For r = 11 To 69
           If .Range("C" & r).Value > 0 And .Range("AD" & r).Value < .Range("N" & r).Value Then
               Msg = .Range("C" & r).Value & vbLf
           End If
       Next r
       If Msg <> "" Then MsgBox Msg & " heeft nog 1 maand tot uitstroom."
       End With
End Sub

Hi, thanks for your reply! But now it only returns the last row wich complies to the criterea.

 

[Fluff]

Oops, it should be

Private Sub Workbook_open()
   Dim r As Long
   Dim Msg As String
   With Sheets("HTL")
       For r = 11 To 69
           If .Range("C" & r).Value > 0 And .Range("W9").Value < .Range("N" & r).Value Then
               Msg = Msg & vbLf & .Range("C" & r).Value
           End If
       Next r
       If Msg <> "" Then MsgBox Msg & vbLf & " heeft nog 1 maand tot uitstroom."
       End With
End Sub