InStr returns 0 when it shouldn't

Looks like you've got extra space or some other non-print character for ("ClientFullName").value

INSTR searches for match of entire string, so if there is extra space in middle, it won't find match.

Edit: Try TRIM and/or WorksheetFunction.Clean on the value before INSTR.

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.




To add a file to a post

Thanks CK76
That in fact was the case, ("ClientFullName") was created with "First" & " " & "Last" as a cell Formula. Apparently adding a space like this is different than typing a space in a string. I will just run InStr twice, once for first and once for last (In other words removing the space). If both are met, then it will run the code.

Thanks! I knew it was something small like that. I would have never have guessed spaces are not equal.

Hello
I tested your code on my own and it works and the result was 1 not 0

Sub Test()
    Dim LoopText As String
    
    Dim sh3
    Set sh3 = ActiveSheet

    LoopText = "John Doe"

    MsgBox ("LoopText = " & LoopText & vbNewLine & _
            "sh3.Range('ClientFullName').value = " & _
            sh3.Range("ClientFullName").Value & vbNewLine & _
            "Instr Returns: " & _
            InStr(LCase(LoopText), LCase(sh3.Range("ClientFullName").Value)))
            
    MsgBox (InStr(LCase(LoopText), LCase(sh3.Range("ClientFullName").Value)))
End Sub

It is difficult to debug from a picture. You insist that InStr returns 0 even when the string and substring values are identical. What have you done to ascertain for certain that they are identical? If I were debugging this, I would focus my efforts on verifying that they are indeed identical as you claim. Some tests I would probably run:

1) A character by character comparison of the search string and the sub string. Looking at the picture of your message box, I see a larger space between John Doe in LoopText and the cell value. Is it possible there are two space characters there? Is it possible that they are different space characters (regular space code 32 vs non-breaking space code 160 vs. maybe an m-space character code 8195)?
2) What does InStr() return for "john" or "doe" or "hn" or other combination of substring?
3) What do I get from Len(substring) versus Len(looptext)?

This sort of question usually comes down to validating/falsifying the OP's claim that the two strings are identical, so I would focus my efforts there.