Modified vb - but it won't work

[Guest]

I am trying to modifiy an existing routine but can't get it to work.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim l As Long

If Target.Count > 1 Then Exit Sub
If Target.Address = "$C$16" Then
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
r = Cells(Rows.Count, 1).End(xlUp).Row
If Target.Value = Sheets("Lists").Range("$A$1").Value Then
Sheets("Lists").Range("$C$2").Value = ""
Else
Sheets("Lists").Range("$C$2").Value = Target.Value
End If

Range("$a20:$P" & r).AdvancedFilter Action:=xlFilterInPlace, _
criteriaRange:=Sheets("Lists").Range("$C$1:$C$2"), Unique:=False
End If

I'm using this in another workbook. The target address is "B2" on worksheet
"code activity". "B1:B2" is my range and the filter column begins at "A10".
Isn't it as simple as substituting this out? I'm guessing my problem is
this new workbook has everything on one worksheet and the original workbook
is checking two worksheets to get the info. I have this booogered up! Any
help?
Thanks,
Greg

 

[Bob Phillips]

This worked for me

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim l As Long

If Target.Count > 1 Then Exit Sub
If Target.Address = "$C$16" Then
If Me.FilterMode Then
Me.ShowAllData
End If
r = Cells(Rows.Count, 1).End(xlUp).Row
If Target.Value = Range("$A$1").Value Then
Range("$C$2").Value = ""
Else
Range("$C$2").Value = Target.Value
End If

Range("$a20:$P" & r).AdvancedFilter Action:=xlFilterInPlace, _
criteriaRange:=Range("$C$1:$C$2"), Unique:=False
End If
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Bob,
I guess I'm not the brightest crayon in the box (great copier). I've spent
most the morning trying to get your routine to work also - no success. What
does 'me' mean? My worksheet says: Option Explicit and I do have another vb

 

[Bob Phillips]

Shouldn't be Greg.

Me refers to the code container, in this case the worksheet that it is in.
Is your code in the worksheet module?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Bob,
It's under the worksheet 'view code'.
Greg

 

[Bob Phillips]

In what way does it not work?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Bob,
The rows that do not match the criteria should be hidden, leaving only the
criteria range rows. Greg