Comparing data between 2 worksheets, mark them as "yes' or "no" based on several condition

Hello guys.

here i got two worksheets. By comparing data between those two worksheets , i want to solve logic function and mark the result with "yes" or "No" along column H (sheet1 -" ). The "yes'" result can be obtained through multiple cases (OR). But in every cases, it must fulfill both condition A and condition B (AND).


So for the example, let say we plug in the formula in H18 :

case1
if column D18 (sheet1) = "Xtpaint" or " paint" or "whtpaint" ,
condition A - A10(sheet1) = A:A (sheet2)
condition B - Let say A18(sheet1) above is equal to A250(sheet2) ,then if B250 = "WDS"
Result - H18="Yes"


case2
if column D18 (Sheet1) = "qc"
condition A - A10(sheet1) = A:A (sheet2)
condition B - Let say A18(sheet1) is equal to A100(sheet2) ,then if B100 = "coating"
Result - H18= "Yes"


case3
if column D18 (Sheet1) = "W/H"
condition A - A10(sheet1) = A:A (sheet2)
condition B - Let say A18(sheet1) is equal to A333(sheet2) ,then if B333 = "Airsheet"
Result - H18= "Yes"

I call sheet1 as "checklist2" and "sheet2 as "submitted"

One guy has gave me this code. it fulfill all the condition at first, and i did a litle modification to suit my needs.

Option Compare Text

Sub comparing3()

    Dim a, b, i As Long
    With Sheets("Submitted")
        a = .Range("a1", .Cells.SpecialCells(11)).Resize(, 3).Value
    End With
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(a, 1)
            If a(i, 1) <> "" Then .Item(a(i, 1)) = a(i, 3)
        Next
        With Sheets("Checklist2").Range("a17").CurrentRegion
            a = .Value
            b = .Columns(8).Value
        End With
        For i = 2 To UBound(a, 1)
            b(i, 1) = "No"
            Select Case True
                Case a(i, 4) Like "xtpaint"
                    If .Item(a(i, 1)) = "painting" Then b(i, 1) = "Yes"
                Case a(i, 4) Like "whpaint"
                    If .Item(a(i, 1)) = "painting" Then b(i, 1) = "Yes"
                Case a(i, 4) Like "paint"
                    If .Item(a(i, 1)) = "painting" Then b(i, 1) = "Yes"
                Case a(i, 4) = "qc"
                    If .Item(a(i, 1)) = "coating" Then b(i, 1) = "Yes"
                Case a(i, 4) = "W/H"
                    If .Item(a(i, 1)) = "AIRSHEET" Then b(i, 1) = "Yes"
            End Select
        Next
    End With
    Sheets("Checklist2").Range("a17").CurrentRegion.Columns(8).Value = b
End Sub

It works perfectly until i just found several data has failed above logic condition. You may refer my attachment. I have marked those data that i men, with redhighlight, both in sheet1 and sheet2 just to indicate this code unable to identify them.


defining error.xlsm

I have checked the code for many times, but could not find the error. Really appreaciate if u guys can take a look and detect the error for me.Thanks in advance.