Filter Based on selected cell value

fraufreda

Board Regular
Joined
Oct 14, 2010
Messages
186
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

fraufreda

Board Regular
Joined
Oct 14, 2010
Messages
186
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
186
Solved :). thanks all for passing by. if anyone wants any help in this Id be happy to give you a hand.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,441
Members
430,548
Latest member
hh_dh2001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top