I am wanting to streamline and simplify this section of code

[FastBob007]

This VBA code works great, however I want a way to streamline and simplify the coding and make a more elegant way of running the task.

There is a message box or boxes that pop up when I open the workbook. The message box tells me who needs to be paid, how much, when and what the payment is for.

I am hoping there is a more elegant way to carry out this task.

Bridgestone Referral Scheme 4.1.xlsm
	E
26
Employee Referrals

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C8:S1048576	Expression	=AND($F8<>"",$F8=$P8)	text	NO
C8:L1048576	Expression	=AND(MEDIAN(TODAY()+1,$J8,TODAY()+7)=$J8,$L8<>"PAID",$E8<>"Resigned",$C8<>"")	text	NO
C8:L1048576	Expression	=AND($J8=TODAY(),$L8<>"PAID",$E8<>"Resigned",$C8<>"")	text	NO
C8:L1048576	Expression	=AND($J8<TODAY(),$L8<>"PAID",$E8<>"Resigned",$C8<>"",$G8<>"",$J8<>"",$M8<>"")	text	NO
C8:O1048576	Expression	=AND(MEDIAN(TODAY()+1,$M8,TODAY()+7)=$M8,$O8<>"PAID",$E8<>"Resigned",$C8<>"")	text	NO
C8:O1048576	Expression	=AND($M8=TODAY(),$O8<>"PAID",$E8<>"Resigned",$C8<>"")	text	NO
C8:O1048576	Expression	=AND($M8<TODAY(),$O8<>"PAID",$E8<>"Resigned",$C8<>"",$G8<>"",$J8<>"",$M8<>"")	text	NO
C8:S1048576	Expression	=AND($E8="Resigned",$C8<>"",$D8<>"")	text	NO
C8:I1048576	Expression	=AND(MEDIAN(TODAY()+1,$G8,TODAY()+7)=$G8,$I8<>"PAID",$E8<>"Resigned",$C8<>"")	text	NO
C8:I1048576	Expression	=AND($G8=TODAY(),$I8<>"PAID",$E8<>"Resigned",$C8<>"")	text	NO
C8:I1048576	Expression	=AND($G8<TODAY(),$I8<>"PAID",$E8<>"Resigned",$C8<>"",$G8<>"",$J8<>"",$M8<>"")	text	NO

The code in question,

Private Sub Workbook_Open()
Dim wks As Worksheet
    For Each wks In ActiveWorkbook.Worksheets
       wks.Protect , UserInterfaceOnly:=True
    Next wks
   
    Worksheets("Employee Referrals").Activate
   
    Dim LastRow As Long
    With SheetEmployeeReferrals
   
        ' Get the last filled row of data
        LastRow = .Cells(.Rows.Count, 3).End(xlUp).row

        ' Select the sheet
        Sheets("Employee Referrals").Select

        For Each row In Sheets("Employee Referrals").Range("C8:S" & LastRow)
       
        ' Payment overdue check for column G
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 5).Value <> "" Then
                If row.Cells(1, 5).Value < Date Then
                If row.Cells(1, 7).Value = "" Then
                If row.Cells(1, 17).Value = "On" Then
               
        ' Message box text for first payment overdue
                MsgBox "There is a first referral payment of " & Format(row.Cells(1, 6).Value, "$#,###") & " overdue for " & row.Cells(1, 1).Value & "", vbExclamation, "Payment overdue"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                   
        ' Payment overdue check for column J
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 8).Value <> "" Then
                If row.Cells(1, 8).Value < Date Then
                If row.Cells(1, 10).Value = "" Then
                If row.Cells(1, 17).Value = "On" Then
               
        ' Message box text for second payment overdue
                MsgBox "There is a second referral payment of " & Format(row.Cells(1, 9).Value, "$#,###") & " overdue for " & row.Cells(1, 1).Value & "", vbExclamation, "Payment overdue"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                               
         ' Payment overdue check for column M
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 11).Value <> "" Then
                If row.Cells(1, 11).Value < Date Then
                If row.Cells(1, 13).Value = "" Then
                If row.Cells(1, 17).Value = "On" Then
               
               
        ' Message box text for third payment overdue
                MsgBox "There is a third referral payment of " & Format(row.Cells(1, 12).Value, "$#,###") & " overdue for " & row.Cells(1, 1).Value & "", vbExclamation, "Payment overdue"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
       
        ' Payment due today check for column G
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 5).Value <> "" Then
                If row.Cells(1, 5).Value = Date Then
                If row.Cells(1, 7).Value = "" Then
                If row.Cells(1, 15).Value = "On" Then
               
        ' Message box text for first payment due today
                MsgBox "There is a first referral payment of " & Format(row.Cells(1, 6).Value, "$#,###") & " due today for " & row.Cells(1, 1).Value & "", vbInformation, "Payment due today"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                   
        ' Payment due today check for column J
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 8).Value <> "" Then
                If row.Cells(1, 8).Value = Date Then
                If row.Cells(1, 10).Value = "" Then
                If row.Cells(1, 15).Value = "On" Then
               
               
        ' Message box text for second payment due today
                MsgBox "There is a second referral payment of " & Format(row.Cells(1, 9).Value, "$#,###") & " due today for " & row.Cells(1, 1).Value & "", vbInformation, "Payment due today"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                               
         ' Payment due today check for column M
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 11).Value <> "" Then
                If row.Cells(1, 11).Value = Date Then
                If row.Cells(1, 13).Value = "" Then
                If row.Cells(1, 15).Value = "On" Then
               
               
        ' Message box text for third payment due today
                MsgBox "There is a third referral payment of " & Format(row.Cells(1, 12).Value, "$#,###") & " due today for " & row.Cells(1, 1).Value & "", vbInformation, "Payment due today"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                                   
        ' Payment due soon check for column G
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 5).Value <> "" Then
                If row.Cells(1, 5).Value > Date Then
                If row.Cells(1, 5).Value < Date + 8 Then
                If row.Cells(1, 7).Value = "" Then
                If row.Cells(1, 16).Value = "On" Then
               
        ' Message box text for first payment due soon
                MsgBox "There is a first referral payment of " & Format(row.Cells(1, 6).Value, "$#,###") & " due soon for " & row.Cells(1, 1).Value & "", vbInformation, "Payment due soon"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                                        End If
                   
        ' Payment due soon check for column J
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 8).Value <> "" Then
                If row.Cells(1, 8).Value > Date Then
                If row.Cells(1, 8).Value < Date + 8 Then
                If row.Cells(1, 10).Value = "" Then
                If row.Cells(1, 16).Value = "On" Then
               
        ' Message box text for second payment due soon
                MsgBox "There is a second referral payment of " & Format(row.Cells(1, 9).Value, "$#,###") & " due soon for " & row.Cells(1, 1).Value & "", vbInformation, "Payment due soon"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                                        End If
                               
         ' Payment due soon check for column M
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 11).Value <> "" Then
                If row.Cells(1, 11).Value > Date Then
                If row.Cells(1, 11).Value < Date + 8 Then
                If row.Cells(1, 13).Value = "" Then
                If row.Cells(1, 16).Value = "On" Then
               
               
        ' Message box text for third payment due soon
                MsgBox "There is a third referral payment of " & Format(row.Cells(1, 12).Value, "$#,###") & " due soon for " & row.Cells(1, 1).Value & "", vbInformation, "Payment due soon"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                                        End If
                                           
        Next
    End With
   
   
    Dim LastRow2 As Long
    With SheetSignOnBonus
   
        ' Get the last filled row of data
        LastRow2 = .Cells(.Rows.Count, 3).End(xlUp).row

        ' Select the sheet
        Sheets("Sign On Bonus").Select

        For Each row In Sheets("Sign On Bonus").Range("C8:V" & LastRow)
       
        ' Payment overdue check for column H
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 6).Value <> "" Then
                If row.Cells(1, 6).Value < Date Then
                If row.Cells(1, 8).Value = "" Then
                If row.Cells(1, 21).Value = "On" Then
               
        ' Message box text for first payment overdue
                MsgBox "There is a first bonus payment of " & Format(row.Cells(1, 7).Value, "$#,###") & " overdue for " & row.Cells(1, 1).Value & "", vbExclamation, "Payment overdue"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                   
        ' Payment overdue check for column K
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 9).Value <> "" Then
                If row.Cells(1, 9).Value < Date Then
                If row.Cells(1, 11).Value = "" Then
                If row.Cells(1, 21).Value = "On" Then
               
        ' Message box text for second payment overdue
                MsgBox "There is a second bonus payment of " & Format(row.Cells(1, 10).Value, "$#,###") & " overdue for " & row.Cells(1, 1).Value & "", vbExclamation, "Payment overdue"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                               
         ' Payment overdue check for column N
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 12).Value <> "" Then
                If row.Cells(1, 12).Value < Date Then
                If row.Cells(1, 14).Value = "" Then
                If row.Cells(1, 21).Value = "On" Then
               
        ' Message box text for third payment overdue
                MsgBox "There is a third bonus payment of " & Format(row.Cells(1, 13).Value, "$#,###") & " overdue for " & row.Cells(1, 1).Value & "", vbExclamation, "Payment overdue"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                           
        ' Payment overdue check for column Q
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 15).Value <> "" Then
                If row.Cells(1, 15).Value < Date Then
                If row.Cells(1, 17).Value = "" Then
                If row.Cells(1, 21).Value = "On" Then
               
        ' Message box text for fourth payment overdue
                MsgBox "There is a fourth bonus payment of " & Format(row.Cells(1, 16).Value, "$#,###") & " overdue for " & row.Cells(1, 1).Value & "", vbExclamation, "Payment overdue"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
       
        ' Payment due today check for column H
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 6).Value <> "" Then
                If row.Cells(1, 6).Value = Date Then
                If row.Cells(1, 8).Value = "" Then
                If row.Cells(1, 19).Value = "On" Then
               
        ' Message box text for first payment due today
                MsgBox "There is a first bonus payment of " & Format(row.Cells(1, 7).Value, "$#,###") & " due today for " & row.Cells(1, 1).Value & "", vbInformation, "Payment due today"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                   
        ' Payment due today check for column K
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 9).Value <> "" Then
                If row.Cells(1, 9).Value = Date Then
                If row.Cells(1, 11).Value = "" Then
                If row.Cells(1, 19).Value = "On" Then
               
        ' Message box text for second payment due today
                MsgBox "There is a second bonus payment of " & Format(row.Cells(1, 10).Value, "$#,###") & " due today for " & row.Cells(1, 1).Value & "", vbInformation, "Payment due today"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                               
         ' Payment due today check for column N
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 12).Value <> "" Then
                If row.Cells(1, 12).Value = Date Then
                If row.Cells(1, 14).Value = "" Then
                If row.Cells(1, 19).Value = "On" Then
               
        ' Message box text for third payment due today
                MsgBox "There is a third bonus payment of " & Format(row.Cells(1, 13).Value, "$#,###") & " due today for " & row.Cells(1, 1).Value & "", vbInformation, "Payment due today"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                       
        ' Payment due today check for column Q
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 15).Value <> "" Then
                If row.Cells(1, 15).Value = Date Then
                If row.Cells(1, 17).Value = "" Then
                If row.Cells(1, 19).Value = "On" Then
               
               
        ' Message box text for fourth payment due today
                MsgBox "There is a fourth bonus payment of " & Format(row.Cells(1, 16).Value, "$#,###") & " due today for " & row.Cells(1, 1).Value & "", vbInformation, "Payment due today"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                                   
        ' Payment Due Soon check for column H
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 6).Value <> "" Then
                If row.Cells(1, 6).Value > Date Then
                If row.Cells(1, 6).Value < Date + 8 Then
                If row.Cells(1, 8).Value = "" Then
                If row.Cells(1, 20).Value = "On" Then
               
        ' Message box text for first payment due soon
                MsgBox "There is a first bonus payment of " & Format(row.Cells(1, 7).Value, "$#,###") & " due soon for " & row.Cells(1, 1).Value & "", vbInformation, "Payment due soon"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                                        End If
                                                                               
        ' Payment due soon check for column K
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 9).Value <> "" Then
                If row.Cells(1, 9).Value > Date Then
                If row.Cells(1, 9).Value < Date + 8 Then
                If row.Cells(1, 11).Value = "" Then
                If row.Cells(1, 20).Value = "On" Then
               
        ' Message box text for second payment due soon
                MsgBox "There is a second bonus payment of " & Format(row.Cells(1, 10).Value, "$#,###") & " due soon for " & row.Cells(1, 1).Value & "", vbInformation, "Payment due soon"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                                        End If
                               
         ' Payment due soon check for column N
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 12).Value <> "" Then
                If row.Cells(1, 12).Value > Date Then
                If row.Cells(1, 12).Value < Date + 8 Then
                If row.Cells(1, 14).Value = "" Then
                If row.Cells(1, 20).Value = "On" Then
               
        ' Message box text for third payment due soon
                MsgBox "There is a third bonus payment of " & Format(row.Cells(1, 13).Value, "$#,###") & " due soon for " & row.Cells(1, 1).Value & "", vbInformation, "Payment due soon"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                                        End If
                                                                                                     
        ' Payment due soon check for column Q
                If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1, 2).Value <> "" Then
                If row.Cells(1, 3).Value = "Employed" Then
                If row.Cells(1, 4).Value <> "" Then
                If row.Cells(1, 15).Value <> "" Then
                If row.Cells(1, 15).Value > Date Then
                If row.Cells(1, 15).Value < Date + 8 Then
                If row.Cells(1, 17).Value = "" Then
                If row.Cells(1, 20).Value = "On" Then
               
        ' Message box text for fourth payment due soon
                MsgBox "There is a fourth bonus payment of " & Format(row.Cells(1, 16).Value, "$#,###") & " due soon for " & row.Cells(1, 1).Value & "", vbExclamation, "Payment due soon"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                                        End If
                                                                         
        Next
    End With
   
End Sub

 

[Herakles]

What do you do when each of these mesaages pops up?

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[FastBob007]

What do you do when each of these mesaages pops up?

I take note off the message and then click 'OK'. Then I action the payment and update the spreadsheet via the user forms.

 

[FastBob007]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Thank you for the advice and for adding the tags to the code.

 

[Herakles]

How about having a temporary worksheet containing a list of all payments that need to be made?

This could be driven from the Userform.

You would not need to write anything down then which would save you a lot of time.

I have developed a prototype but I need a sample of your data to include column headers.

Can you post an image of the Userform?

 

[FastBob007]

I should have been clearer. All I am wanting to do is to see if there is a simpler way to write the code I have already written.
I am not looking to add worksheets.
I do not write anything down, the message box tells me who needs to be paid and then they get paid and then updated in the worksheet via a user form.

Perhaps there is not a different way to write the code for the same functionality, in which case I will just use what I have written.

 

[Herakles]

Of course there is a better way.

By using filters you will be able to identify which rows need to be paid.

How do you access the worksheet to update it when the messages are appearing?

 

[offthelip]

you can speed up your existing code very easily by loading the data into variant arrays and doing two edits.. I have modified the first sheet which involves adding two lines of code, changing two and two edits over the range fo the workhseet.:

Private Sub Workbook_Open()
Dim wks As Worksheet
    For Each wks In ActiveWorkbook.Worksheets
       wks.Protect , UserInterfaceOnly:=True
    Next wks
   
    Worksheets("Employee Referrals").Activate
   
    Dim LastRow As Long
    With SheetEmployeeReferrals
   
        ' Get the last filled row of data
        LastRow = .Cells(.Rows.Count, 3).End(xlUp).Row

        ' Select the sheet
        Sheets("Employee Referrals").Select

'        For Each Row In Sheets("Employee Referrals").Range("C8:S" & LastRow) ' change this to
         inarr = Sheets("Employee Referrals").Range("C8:S" & LastRow)    ' add this line
         For i = 1 To UBound(inarr)                                      ' and this line
        ' Payment overdue check for column G
                If inarr(i, 1) <> "" Then
                If inarr(i, 2) <> "" Then
                If inarr(i, 3) = "Employed" Then
                If inarr(i, 4) <> "" Then
                If inarr(i, 5) <> "" Then
                If inarr(i, 5) < Date Then
                If inarr(i, 7) = "" Then
                If inarr(i, 17) = "On" Then
               
        ' Message box text for first payment overdue
                MsgBox "There is a first referral payment of " & Format(inarr(i, 6), "$#,###") & " overdue for " & inarr(i, 1) & "", vbExclamation, "Payment overdue"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                   
        ' Payment overdue check for column J
                If inarr(i, 1) <> "" Then
                If inarr(i, 2) <> "" Then
                If inarr(i, 3) = "Employed" Then
                If inarr(i, 4) <> "" Then
                If inarr(i, 8) <> "" Then
                If inarr(i, 8) < Date Then
                If inarr(i, 10) = "" Then
                If inarr(i, 17) = "On" Then
               
        ' Message box text for second payment overdue
                MsgBox "There is a second referral payment of " & Format(inarr(i, 9), "$#,###") & " overdue for " & inarr(i, 1) & "", vbExclamation, "Payment overdue"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                               
         ' Payment overdue check for column M
                If inarr(i, 1) <> "" Then
                If inarr(i, 2) <> "" Then
                If inarr(i, 3) = "Employed" Then
                If inarr(i, 4) <> "" Then
                If inarr(i, 11) <> "" Then
                If inarr(i, 11) < Date Then
                If inarr(i, 13) = "" Then
                If inarr(i, 17) = "On" Then
               
               
        ' Message box text for third payment overdue
                MsgBox "There is a third referral payment of " & Format(inarr(i, 12), "$#,###") & " overdue for " & inarr(i, 1) & "", vbExclamation, "Payment overdue"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
       
        ' Payment due today check for column G
                If inarr(i, 1) <> "" Then
                If inarr(i, 2) <> "" Then
                If inarr(i, 3) = "Employed" Then
                If inarr(i, 4) <> "" Then
                If inarr(i, 5) <> "" Then
                If inarr(i, 5) = Date Then
                If inarr(i, 7) = "" Then
                If inarr(i, 15) = "On" Then
               
        ' Message box text for first payment due today
                MsgBox "There is a first referral payment of " & Format(inarr(i, 6), "$#,###") & " due today for " & inarr(i, 1) & "", vbInformation, "Payment due today"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                   
        ' Payment due today check for column J
                If inarr(i, 1) <> "" Then
                If inarr(i, 2) <> "" Then
                If inarr(i, 3) = "Employed" Then
                If inarr(i, 4) <> "" Then
                If inarr(i, 8) <> "" Then
                If inarr(i, 8) = Date Then
                If inarr(i, 10) = "" Then
                If inarr(i, 15) = "On" Then
               
               
        ' Message box text for second payment due today
                MsgBox "There is a second referral payment of " & Format(inarr(i, 9), "$#,###") & " due today for " & inarr(i, 1) & "", vbInformation, "Payment due today"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                               
         ' Payment due today check for column M
                If inarr(i, 1) <> "" Then
                If inarr(i, 2) <> "" Then
                If inarr(i, 3) = "Employed" Then
                If inarr(i, 4) <> "" Then
                If inarr(i, 11) <> "" Then
                If inarr(i, 11) = Date Then
                If inarr(i, 13) = "" Then
                If inarr(i, 15) = "On" Then
               
               
        ' Message box text for third payment due today
                MsgBox "There is a third referral payment of " & Format(inarr(i, 12), "$#,###") & " due today for " & inarr(i, 1) & "", vbInformation, "Payment due today"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                                   
        ' Payment due soon check for column G
                If inarr(i, 1) <> "" Then
                If inarr(i, 2) <> "" Then
                If inarr(i, 3) = "Employed" Then
                If inarr(i, 4) <> "" Then
                If inarr(i, 5) <> "" Then
                If inarr(i, 5) > Date Then
                If inarr(i, 5) < Date + 8 Then
                If inarr(i, 7) = "" Then
                If inarr(i, 16) = "On" Then
               
        ' Message box text for first payment due soon
                MsgBox "There is a first referral payment of " & Format(inarr(i, 6), "$#,###") & " due soon for " & inarr(i, 1) & "", vbInformation, "Payment due soon"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                                        End If
                   
        ' Payment due soon check for column J
                If inarr(i, 1) <> "" Then
                If inarr(i, 2) <> "" Then
                If inarr(i, 3) = "Employed" Then
                If inarr(i, 4) <> "" Then
                If inarr(i, 8) <> "" Then
                If inarr(i, 8) > Date Then
                If inarr(i, 8) < Date + 8 Then
                If inarr(i, 10) = "" Then
                If inarr(i, 16) = "On" Then
               
        ' Message box text for second payment due soon
                MsgBox "There is a second referral payment of " & Format(inarr(i, 9), "$#,###") & " due soon for " & inarr(i, 1) & "", vbInformation, "Payment due soon"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                                        End If
                               
         ' Payment due soon check for column M
                If inarr(i, 1) <> "" Then
                If inarr(i, 2) <> "" Then
                If inarr(i, 3) = "Employed" Then
                If inarr(i, 4) <> "" Then
                If inarr(i, 11) <> "" Then
                If inarr(i, 11) > Date Then
                If inarr(i, 11) < Date + 8 Then
                If inarr(i, 13) = "" Then
                If inarr(i, 16) = "On" Then
               
               
        ' Message box text for third payment due soon
                MsgBox "There is a third referral payment of " & Format(inarr(i, 12), "$#,###") & " due soon for " & inarr(i, 1) & "", vbInformation, "Payment due soon"
                    Else
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
                                                    End If
                                                        End If
                                           
        Next i                 ' change this line
    End With

the edits are:

 

[Georgiboy]

Then maybe to make it more readable you could remove a lot of the IFS as below:

            ' Payment overdue check for column G
            If inarr(i, 1) <> "" And _
                    inarr(i, 2) <> "" And _
                    inarr(i, 3) = "Employed" And _
                    inarr(i, 4) <> "" And _
                    inarr(i, 5) <> "" And _
                    inarr(i, 5) < Date And _
                    inarr(i, 7) = "" And _
                    inarr(i, 17) = "On" Then
                MsgBox "There is a first referral payment of " & Format(inarr(i, 6), "$#,###") & " overdue for " & inarr(i, 1) & "", vbExclamation, "Payment overdue"
            End If