Macro Event when cell changes it should copy to another sheet after there is a no match

Hi Ross,
the code is my existing one. In that i need to add an event target for other column. As two separate macros cannot be used for same event like worksheet change. I want to include for other column to.
Existing event is when you click on the cell in target column then it copies the value .
Now what i want is column where copied value goes is my new target. I need to check the cell value(text) in the column to another sheet.

THe another sheet contains, a group of text value which matches with the new target.
When the new target has new value or text, that should be copied to this sheet column A.

I am of the idea to use loop to check for matches, but since the another sheet has around 1000 rows, i want to put this in a dynamic array and compare this array to the target cell ..
Here my issue , is how to create dynamic array of the column, then how to compare the array. Then after new value found, and copied, how to add that row in the array(ie how to increase the array size).
The file is too huge, if you want i will create a dummy sample and provide you, if you are familiar and good with array, as mine is poor.

Hi Ross and All ,
I have attached the sample file.

What i want is, if i add the data in sheet 2 col A and B.. it should compare with sheet 1 in respective columns
and should include as an addition to in next row, if it is not available.
In the sheet2 coloured cell, is a new case and school, which doesn't exist in sheet1.
So it should be added. It should be compared and if there is no match, then it should be added.
Both the cells(col a and b in sheet2) needed to be added in the same row of sheet 1.
I have more than 500 rows of sheet 1 in the original file. Hence i want to put the sheet 1 both columns in array and then comparison take place.
This event takes place when the cell changes.
THis is to facilitate, when an already existing detail is entered in Col A of sheet 2, automatically col B should be updated.
This will be done, only when new entries are added to the sheet 1 next empty row.

According to your attachment a starter to paste to the Sheet2 worksheet module :

PHP Code: 

Private Sub Worksheet_Change(ByVal Target As Range)
                       Dim Rg As Range, A$
    If Target.CountLarge = 1 Then
        With Sheet1.UsedRange.Columns(1)
            If Target.Column = 1 Then
                If Target.Row = Me.UsedRange.Rows.Count Then
                       Set Rg = .Find(Target.Value2, , xlValues, xlWhole)
                    If Not Rg Is Nothing Then
                        Application.EnableEvents = False
                        Target(1, 2).Value2 = Rg(1, 2).Value2
                        Application.EnableEvents = True
                        Set Rg = Nothing
                    End If
                End If
            ElseIf Target.Column = 2 Then
                   Set Rg = .Find(Target(1, 0).Value2, , xlValues, xlWhole)
                If Not Rg Is Nothing Then
                           A = Rg.Address
                    Do
                        If Target.Value2 = Rg(1, 2).Value2 Then Set Rg = Nothing: Exit Sub
                           Set Rg = .FindNext(Rg)
                    Loop Until Rg.Address = A
                           Set Rg = Nothing
                End If
                    Target(1, 0).Resize(, 2).Copy .Cells(.Rows.Count + 1, 1)
            End If
        End With
    End If
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !

A bit late : nothing in the initial post neither in the attachment !
So just replace the old event by mine as it seems to not need to merge anything.
But add the same EnableEvents codelines for the Copy in the Column = 2 part …

Hi Marc,
In the above. as the new entries are copied to another sheet. I want one another.
When in sheet 2 col A user types, if it is already available in the previous row of the same column, then for column B value of the target row should be automatically updated.
For eg:
If i type case 4 in col A, if this exist in prev row.. the corresp. value in col B for this..
should be automatically populate in col B of the target row when i type case 4 .

Hi Marc,
And one doubt.. sometimes the event does not pickup and copies to another sheet.
When i debug, in those cases i see RG as nothing.. i am not able to understand why the range rg does not pick up the find value?
This happens sometime and not always, and not able to find pattern as to why this happens.

Maybe a cell has a space so does not match …