value exists in multiple ranges not working

[KDS14589]

my present code is not working.......

Sub NotAssigned()
Call On_Off_Stuff.TurnOffStuff

Dim AddOnLimit As Long
    AddOnLimit = AA04.Range("AO8")
Dim WorkSheetLimit As Long
    WorkSheetLimit = AA04.Range("AO11")
    
With AA04
    .Range("AD5:AD" & AddOnLimit).Value = "=IF(ISBLANK(RC[-1]),"""",IF(ISERROR(MATCH(RC[-1],R5C27:R" & LDR_EH_4_04 & "C27,0)),""Not Assigned"",""Used"" ))"    'STATUS
    .Range("AI5:AI" & WorkSheetLimit).Value = "=IF(ISBLANK(RC[-1]),"""",IF(ISERROR(MATCH(RC[-1],R5C27:R" & LDR_EH_4_04 & "C27,0)),""Not Assigned"",""Used"" ))" ' status
End With

Dim AddOnRange As Range
    Set AddOnRange = AA04.Range("AD5:AD" & AddOnLimit)
Dim WorksheetRange As Range
    Set WorksheetRange = AA04.Range("AI5:AI" & WorkSheetLimit)
Dim MyRange As Range
    Set MyRange = Union(AddOnRange, WorksheetRange)
Dim NoColor As String
    NoColor = "Not Assigned"
Dim res As Variant
    res = Application.Match(AddOnRange, NoColor, 0)
Dim res2 As Variant
    res2 = Application.Match(WorksheetRange, NoColor, 0)

  If Not IsError(res) And Not IsError(res2) Then 'NoColor is present
       Load MsgBoxMatchNo
       MsgBoxMatchNo.Show
       Exit Sub
  Else
       Load MsgBoxMatchYes
       MsgBoxMatchYes.Show
       Exit Sub
  End If

Call On_Off_Stuff.TurnOnStuff
End Sub

PROBLEM
I have 2 ranges that are populated with either 'Used' or 'Not Assigned'. If 'Not Assigned' is present in a cell in either of the 2 ranges, then MsgBoxMatchNo (my own designed MsgBox ) will appear just once even if 'Not Assigned' is present in several cells, but if there are no cells in either range with 'Not Assigned' then MsgBoxMatchYes will appear just once.
My problem is I get multiple MsgBoxMatchNo even when there is only 1 cell with 'Not Assigned'

 

[Micron]

I'm not well versed in worksheet functions (and don't want to be) so I'm guessing that your worksheet function is returning several results, although I see no reason for that to trigger opening a form multiple times. Perhaps it is due to your calls to other procedures. I'm wondering why you don't just use .Find method of your range(s) and if a key word is found, present the appropriate message? That would only happen once, as long as you don't repeat the .Find method in the same event.

Did you step through the code and watch the flow and check your variables as you go?