Compare 2 Columns in 2 Sheets that are Not Ordered the Same
 

Hi,
In matching 2 columns-col J in sheet1 and col A in sheet2-col A in
sheet2 is a subset of col J and can contain any values of col J. Col A
is ordered differently than col J, and col A does not have to contain
all the values of col J. Col J can contain more (different) values
than col A.

What I want to do is for sheet1 to detect when a change occurs in an
existing value in col J that is also present in sheet2 col A. If that
cell in col J is changed, I would like to invoke the removal of a
filter (col A is the filtered list).

Thanks for any help,

Eric

Compare 2 Columns in 2 Sheets that are Not Ordered the Same
 

Right click on the sheet tab of Sheet1 and select view code. Then paste in
code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng2 As Range, v, v1
Dim cell As Range, num As Long
If Target.Count 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Target.Column = 10 Then
With Worksheets("Sheet2")
Set rng2 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
On Error GoTo ErrHandler
Application.EnableEvents = False
v = Target.Value
Application.Undo
v1 = Target.Value

num = Application.CountIf(rng2, v1)
Target.Value = v
If num 0 Then
If Worksheets("Sheet2").FilterMode Then
Worksheets("Sheet2").ShowAll
' or to remove the autofilter
'Worksheets("Sheet2").Autofiltermode = False
End If
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Arnold" wrote in message
oups.com...
Hi,
In matching 2 columns-col J in sheet1 and col A in sheet2-col A in
sheet2 is a subset of col J and can contain any values of col J. Col A
is ordered differently than col J, and col A does not have to contain
all the values of col J. Col J can contain more (different) values
than col A.

What I want to do is for sheet1 to detect when a change occurs in an
existing value in col J that is also present in sheet2 col A. If that
cell in col J is changed, I would like to invoke the removal of a
filter (col A is the filtered list).

Thanks for any help,

Eric

Compare 2 Columns in 2 Sheets that are Not Ordered the Same
 

Thanks Tom.