Selection change on different worksheet

[frozenconicbeans]

Hi i'm trying to find a solution to a problem concerning the selection change event: i have a worksheet with a list of products and all the details related. on another worksheet i have the same products with other info. what i would like to do is clicking on the product cell in the first sheet,havinf it filtered in the second one. the ON/OFF option is to enable/disable this functionality in the file which already has a double-click enabled macro. thanks for looking into it

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngF As Range
Dim rngFS As Range

Dim lRow As Long
Dim lCol As Long
Set rngF = ActiveSheet.AutoFilter.Range
Set rngFS = ActiveSheet.Range("FilterStatus")

lCol = rngF.Columns(1).Column - 1
lRow = rngF.Columns(1).Row

If Target.Count > 1 Then GoTo exitHandler

If Target.Address = rngFS.Address Then
If rngFS.Value = "On" Then
rngFS.Value = "Off"
Else
rngFS.Value = "On"
End If
End If

If UCase(rngFS.Value) = "ON" Then
If Not Intersect(Target, rngF) Is Nothing Then
If Target.Row > lRow Then
rngF.AutoFilter Field:=Target.Column - lCol, _
Criteria1:=Target.Value
ElseIf Target.Row = lRow Then
rngF.AutoFilter Field:=Target.Column - lCol
End If
End If
End If

exitHandler:
Exit Sub

End Sub</code>

 

[NotoriousPopol]

Hi, you never refer to the first or 2nd sheet in your code. Do we agree that you want an event on the first one, and filter in the 2nd one? Try something like :

Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1=ThisWorkbook.Activesheet
Set Ws2=ThisWorkbook.Sheets(2)

...

Set rgnF=Ws2.AutoFilter.Range

...