How to do EXACT match with VBA

[Gen Otmin]

James006 helped me to solve one part of this code, thanks.
The issue I have now is that I thought this to be case sensitive, but it's not.
If I have a file in the folder named 8PQ9800-0AA12_13_aLL.pdf and the search critera is 8PQ9800-0AA12_13_ALL.pdf it should not "find it" and mark it green since the names differ.
In conditional formating i use EXACT to do this with an IF statement =IF($M2="";"";IF(EXACT($N2;$F2);1;0))
But I can't figure out how to do this in the code below.

Sub LookForFiles()
Dim pStr As String, myFile As String, rng As Range, lrw As Long
Dim fList As Variant

pStr = ActiveWorkbook.Path & "\"            'pStr = "The Path to Your Folder Here" 'Path to folder including final \

lrw = Range("E" & Rows.Count).End(xlUp).Row
Set rng = Range("E2", "E" & lrw)
fList = rng.Value
For i = LBound(fList, 1) To UBound(fList, 1)
    myFile = Dir(pStr & fList(i, 1) & "*")
    If Not myFile = vbNullString Then
        If rng.Cells(i).Value <> "" Then
        rng.Cells(i).Interior.Color = RGB(169, 208, 142) 'rng.Cells(i).Interior.Color = vbGreen
    End If
Next i
End Sub

 

[JEC]

But, in windows it’s the same file since the filenames are not case sensitive. So Dir can’t be case sensitive

 

[Gen Otmin]

But, in windows it’s the same file since the filenames are not case sensitive.

Yes I know.
How I have done it before with conditional formating is that I read the files in the folder, put on a sheet, then compare thoose values with my search string within excel itself.
Then I can use EXACT to get the difference between a and A.
I were hoping that it would be possible to do the same in VBA, maybe not with my current code, probably need to change it a lot.
Reason for me to not use conditional formating is that when someone adds a row in the midddle of the table everything below that row stops working.

 

[Peter_SSs]

Try adding the blue code. You are also missing an End If in your snippet (red here)

For i = LBound(fList, 1) To UBound(fList, 1)
    myFile = Dir(pStr & fList(i, 1) & "*")
    If Not myFile = vbNullString Then
        If rng.Cells(i).Value <> "" And rng.Cells(i).Value = myFile Then
          rng.Cells(i).Interior.Color = RGB(169, 208, 142) 'rng.Cells(i).Interior.Color = vbGreen
        End If
    End If
Next i

 

[Peter_SSs]

Try adding the blue code.

Actually you shouldn't need two tests in that line with the blue code above, I think this one test should be sufficient.

For i = LBound(fList, 1) To UBound(fList, 1)
    myFile = Dir(pStr & fList(i, 1) & "*")
    If Not myFile = vbNullString Then
        If rng.Cells(i).Value = myFile Then
          rng.Cells(i).Interior.Color = RGB(169, 208, 142) 'rng.Cells(i).Interior.Color = vbGreen
        End If
    End If
Next i

 

[Gen Otmin]

Actually you shouldn't need two tests in that line with the blue code above, I think this one test should be sufficient.

For i = LBound(fList, 1) To UBound(fList, 1)
    myFile = Dir(pStr & fList(i, 1) & "*")
    If Not myFile = vbNullString Then
        If rng.Cells(i).Value = myFile Then
          rng.Cells(i).Interior.Color = RGB(169, 208, 142) 'rng.Cells(i).Interior.Color = vbGreen
        End If
    End If
Next i

Thank you. Works as a charm

 

[Peter_SSs]

Thank you. Works as a charm

You're welcome. Thanks for the follow-up.