Filter Based on selected cell value

fraufreda

Board Regular
Joined
Oct 14, 2010
Messages
182
hi. I have two tables in two different sheets. I want to filter one of the tables based on the selection of a cell in the other. they both share the same order of headings. Once it's filtered a copy of the filtered range will be pasted next to the other table. then if any entry in this filtered range is selected, both entries in both tables will be highlight with a unique colour. this is what i've come up with so far. I hope someone can help me.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
    Sheets("X").Range("$A$1:$C$2000").AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(2, Sheets("DetailedTable").ActiveCell.Value)
    Sheets("X").Range("$A$1:$C$2000").AutoFilter Field:=3, Criteria1:=Sheets("DetailedTable").ActiveCell.Value
    Sheets("X").Range("$A$1:$C$2000").AutoFilter Field:=2, Operator:= _
        xlFilterValues, Criteria2:=Array(2, Sheets("DETAILEDTable").ActiveCell.Value)
    Sheets("X").Range("$A$1:$D$2000").AutoFilter Field:=4, Operator:= _
        xlFilterNoFill
            Range("A1").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Range(Selection, Selection.End(xlDown)).Select
                Selection.Copy
                Sheets("DETAILEDTable").Select
                Range("F1").Paste
End Sub
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

fraufreda

Board Regular
Joined
Oct 14, 2010
Messages
182
Solved. except for one thing. I have in sheet1 column J row numbers that look like this 11-2. the number before the hyphen is the row number in sheet2 while the one after is the row number in sheet1. Once I select a cell in J I want code that fill the rows in each sheet with with a unique color. any help would be highly appreciated
 

fraufreda

Board Regular
Joined
Oct 14, 2010
Messages
182
Solved :). thanks all for passing by. if anyone wants any help in this Id be happy to give you a hand.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,309
Members
409,862
Latest member
lbisacca
Top