VBA: Prevent condition from being tripped twice for the same range pair

[gravanoc]

My code is setup to check whether manufacturing equipment is assigned to work on a product on the same date. If so, that is a conflict and the row with the conflict is highlighted yellow. That's all good, but I'm trying to think of a straightforward way to eliminate the minor annoyance where it shows the same warning twice. This is because it sets the target range to check against, and iterates through the entire list of other items to search for a conflict.

For example, if C4 and C7 both have the same equipment in their cell, and their adjacent cells D4 and D7 have the same date listed, they will be in conflict, and a message box pops up. This will happen twice though because when C7 is the target, it will still iterate over C4 and give the same warning.

I could solve this by just eliminating the message box, but this might be an important lesson to learn for the future.

Below is the code I'm using, and a link to a 10 second video that demonstrates the code in action.

Sub CrossCheck()

    Dim helperSht As Worksheet
    Dim managerSht As Worksheet
    
    Dim helperRg As Range
    Dim helperRg2 As Range
    Dim highlightRg(1 To 2) As Range
    
    Dim str1 As String
    Dim str2 As String
    
    Dim d As Date
    
    Dim i As Long
    Dim m As Long
    
    Dim strArr() As String
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Set helperSht = Worksheets("Helper")
    Set helperRg = helperSht.Range("F3")
    Set helperRg2 = helperSht.Range("G3")
    
    Set managerSht = Worksheets("Manager Tab")
    
    For i = 0 To helperRg2.End(xlDown).Row
    
        If helperRg2.Offset(i, 0).Value = 0 Or helperRg2.Offset(i, 0).Value = "" Then GoTo NextIteration

        str1 = str1 + helperRg2.Offset(i, 0).Text
        str1 = str1 + " "
    
        
NextIteration:
    
    Next
    
    str1 = Trim(str1)
    
    strArr() = Split(str1, " ")
    
    
    For i = 0 To UBound(strArr)
            
            str2 = helperRg.Offset(i, 0).Value
            m = 0
        For j = 0 To helperRg2.End(xlDown).Row
            
             If helperRg2.Offset(j, 0).Value <> "" Then
            
                d = helperRg2.Offset(j, 0).Value
                
             Else
            
                d = "1/1/20"
                
             End If
            
             If strArr(i) = d And m = 0 And d > DateValue(Now) Then
            
                 m = m + 1
          
            ElseIf strArr(i) = d And m > 0 And d > DateValue(Now) Then
            
                If str2 = helperRg2.Offset(j, -1).Value And i <> j And helperRg2.Offset(j, -1).Value <> 0 Then

                    Set highlightRg(1) = managerSht.Range("A" & helperRg.Offset(i, 0).Row)
                    Set highlightRg(2) = managerSht.Range("A" & helperRg.Offset(j, 0).Row)

                    highlightRg(1).Value = True
                    highlightRg(2).Value = True
                    
                    Application.ScreenUpdating = True

                    MsgBox ("Equipment assigned to separate products on multiple start dates at " + helperRg.Offset(i, 0).Address + " and " + helperRg2.Offset(j, -1).Address + ".")
                    
                    Application.ScreenUpdating = False

               End If
              
             End If
            
        Next
        
    Next
    
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

End Sub

 

[footoo]

Maybe:
Change the MsgBox line to :

If x <> 1 Then
     MsgBox("......")
    x=1
End If

Set the variable back to 0 in the appropriate place ( or maybe Exit For).