Comparing cells from two different worksheets

[zoog25]

Hello all,

Here is my situation, I have Sheet 1 that is titled "Tract Parcels" and Sheet 2 that is titled "NOC". Currently i started a short program that would look at entries made from sheet 1 and if it exists in sheet 2 then a message would appear that says that it already exists, now if it didn't then it would copy the info over.

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Count > 1 Then Exit Sub


'Initiate Work
Dim lrow As Long
Dim NOCws As Worksheet
Dim TPws As Worksheet


Set NOCws = ThisWorkbook.Worksheets("NOC")
Set TPws = ThisWorkbook.Worksheets("Tract Parcels")


'Find last row in NOC Log
lrow = NOCws.Cells(Rows.Count, 5).End(xlUp).Row


Stop


'NOC Auto Entry After Map goes to Council
If Not Intersect(Target, Range("E:F")) Is Nothing Then
    If WorksheetFunction.CountA(Cells(Target.Row, "E").Resize(, 2)) = 2 Then
        With NOCws.Range(Cells(4, "F"), Cells(lrow, "F"))
            Set NOCTP = .Find(Cells(Target.Row, "G"), LookIn:=xlValues)
            If Not NOCTP Is Nothing Then
                
                Do
                Loop While NOCTP.Address <> firstAddress
            End If
        End With
    End If
End If


End Sub

So the above is from code that I found and have manipulated. My problem is that in this situation, it first Take the value in Column G of Sheet 1 and looks for it in Column F of Sheet 2. How my problem is this, if it find a match between the two, i want to add another comparison. Next if say Column H of Sheet 1 Matches Column G of sheet 2 then a message would appear saying that "NOC Entry already made", otherwise the values from columns (A, B, E, D, G, H, & I) from sheet 1 would then be copied over to the first available row in Sheet 2 into columns (B,C,D,E,F,G,I) Respectively.

Now i know the above loops through each instance that the information being search for appears. Ultimately if all instances don't match up then it transfers info, otherwise the message appears saying that it has already been entered.

 

[JLGWhiz]

maybe this

With NOCws.Range(Cells(4, "F"), Cells(lrow, "F"))
    Set NOCTP = .Find(Cells(Target.Row, "G"), LookIn:=xlValues)
        If Not NOCTP Is Nothing Then
            If Cells(Target.Row, "H").Value = .Cells(NOCTP.Row, "G").Value Then
                MsgBox "NOC entry already made.", vbInformation, "ENTRY MADE"
            Else
                .Cells(NOCTP.Row, "B") = Cells(Target.Row, "A").Value
                .Cells(NOCTP.Row, "C") = Cells(Target.Row, "B").Value
                .Cells(NOCTP.Row, "D") = Cells(Target.Row, "E").Value
                .Cells(NOCTP.Row, "E") = Cells(Target.Row, "D").Value
                .Cells(NOCTP.Row, "F") = Cells(Target.Row, "G").Value
                .Cells(NOCTP.Row, "G") = Cells(Target.Row, "H").Value
                .Cells(NOCTP.Row, "I") = Cells(Target.Row, "I").Value
            End If
        End If
End With

 

[zoog25]

Thanks for the assistance JLGWhiz. I'll try running it when i get a chance.