How To Autofilter Rows Based On Cell Value In Excel?

[Rashie]

Hey All,

I'm having a nightmare with this seemingly simple task.

I have several cells as below
[TABLE="width: 666"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD]E USD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E GBP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E EUR[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]O USD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]O USD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]O USD[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And another table of data below with a frozen pane, A10:Z2500.
Using an example of E EUR this data is in column J, O USD is in column K etc
I just want to use a filter function where instead of using the auto filter I can enter an amount next to each cell as above and have it filter automatically.

I can make it work individually but I can't have all 6 functional at the same time.

Any ideas?

Thanks All

Sorry, should have posted my semi successful code too. I just want to have this working at the same time for all 6 data cells.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngTrigger As Range
Dim rngData As Range
Dim lngCol As Long

'----SETUP---
'Which cell are you typing in?
Set rngTrigger = Range("B3")
'Where is the data to be filtered?
Set rngData = Range("A10:Z2500")
'what number column are we filtering of the table?
lngCol = 1
'------------

'Check if user changed our cell of interest?
If Intersect(Target, rngTrigger) Is Nothing Then Exit Sub

Application.ScreenUpdating = False
With rngData
'Clear previous filter
.AutoFilter
'Apply new filter
.AutoFilter field:=lngCol, Criteria1:=rngTrigger.Value
End With

Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

The code says that you filter in column 1, but in your explanation you want to filter in column J, or I'm not really understanding what you need.