look up Range in two sheets

[maheshr68]

Greetings!!

Set of data in Sheet1!ColC:C

http://www.zshare.net/download/869751194725124b/
http://www.zshare.net/download/86975104af052e74/
http://www.duckload.com/play/97AC92EBF4ACA768
http://www.megavideo.com/?d=D9SI4IMA
http://kickload.com/stream/b03df74b28e0
http://www.zshare.net/video/870002826ef4c65f/
http://www.putlocker.com/file/16MTOBHRBMPWOWOK
http://www.putlocker.com/file/39BUVVIGQAECG848
http://fileserve.com/file/6pPNx3F
http://www.megaupload.com/?d=AJZV7L4W
http://www.mediafire.com/?it2diku5zitchz0
http://www.mediafire.com/?jg2zzccubr5chx2
http://ul.to/nt5se2/
http://uploadbox.com/files/b534ade2d0
http://www.filefactory.com/file/b566204/
http://www.filefactory.com/file/b5661hg/
http://www.filefactory.com/file/b5661hd/

When new data is pasted into ColC,

1. The entry should not be pasted and entire row deleted if already present anywhere in Sheet1!C:C
2. The entry should not be pasted and entire row deleted if present in Sheet2!B:B

Set of data in Sheet2!ColB:B

http://www.filesonic.com/file/123400931
http://www.filesonic.com/file/123416301
http://www.filesonic.com/file/123212881
http://www.mediafire.com/?ffhbq59bcabc5of
http://www.mediafire.com/?gbichkqd5cbsv2d
http://www.mediafire.com/?yc64w0gxi4owfzl
http://www.mediafire.com/?3ao72v3om7c4aij
http://www.sendspace.com/file/2vy4gt
http://www.sendspace.com/file/7v6cla
http://www.sendspace.com/file/vhillr
http://filefactory.com/file/b4gb2hh/
http://filefactory.com/file/b4hd2b8/
http://filefactory.com/file/b5239hh/
http://filefactory.com/file/b526293/
http://filefactory.com/file/b527g5g/
http://filefactory.com/file/b54gh1c/
http://fileserve.com/file/AkrHYB8
http://fileserve.com/file/CYAxk9J
http://fileserve.com/file/d7UEv5B
http://fileserve.com/file/DYWAmTk
http://www.filefactory.com/file/b566204/n/
http://www.filefactory.com/file/b5661hg/n/
http://www.filefactory.com/file/b5661hd/n/

Best Regards,
Mahesh

 

[VoG]

Hi. Try this: right click Sheet1's tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And Target.Value <> "" Then
    Application.EnableEvents = False
    If IsNumeric(Application.Match(Target, Sheets("Sheet2").Columns("B"), 0)) _
        Or IsNumeric(Application.Match(Target, Sheets("Sheet2").Columns("C"), 0)) Then Target.EntireRow.ClearContents
    Application.EnableEvents = True
End If
End Sub

 

[maheshr68]

Greetings!!

tested it, it works perfectly for #2, but somehow not for #1


1. The entry should not be pasted and entire row deleted if already present anywhere in Sheet1!C:C
2. The entry should not be pasted and entire row deleted if present in Sheet2!B:B

is there anything i could do

also could i use this as a module?

Best Regards,
Mahesh

 

[VoG]

Sorry, I misread #1. Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And Target.Value <> "" Then
    Application.EnableEvents = False
    If IsNumeric(Application.Match(Target, Sheets("Sheet2").Columns("B"), 0)) _
        Or Application.CountIf(Columns("C"), Target.Value) > 1 Then Target.EntireRow.ClearContents
    Application.EnableEvents = True
End If
End Sub

 

[maheshr68]

Runtime Error 9: Subscript out of range

Debug

If IsNumeric(Application.Match(Target, Sheets("Sheet2").Columns("B"), 0)) _
Or Application.CountIf(Columns("C"), Target.Value) > 1 Then

Now, #2 also does not seem to work

 

[VoG]

Well, if the first version worked I don't see why you would get that error. Have you renamed Sheet2 to something else?

 

[maheshr68]

Will create new workbook, test again

Regards,
Mahesh

 

[maheshr68]

Found out reason why debug error generates - due to pasting multiple data at same time. (Could this be overcome by any method, pls let know)

pasting only one data does not generate any error and both #1 and #2 work perfectly.

Best Regards,
Mahesh

 

[VoG]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Target
    If c.Column = 3 And c.Value <> "" Then
        Application.EnableEvents = False
        If IsNumeric(Application.Match(c.Value, Sheets("Sheet2").Columns("B"), 0)) _
            Or Application.CountIf(Columns("C"), c.Value) > 1 Then c.EntireRow.ClearContents
        Application.EnableEvents = True
    End If
Next c
End Sub

 

[maheshr68]

Worked perfectly now!!

Best Regards,
Mahesh