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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
|
Similar Threads
