Colored and filtered cells
Change the upper case "F" in "Fastener to a lower case "f"
Or place Option Compare Text above the subs.
Gord Dibben MS Excel MVP
On Wed, 9 Jan 2008 13:40:03 -0800, Tony S.
wrote:
Can anyone tell me why the following routine colors and filters the first two
entries ("joint" and "hole") but the 3rd ("Fastener") just filters? I'm
using excel 2003.
Sub joint()
Dim r As Range
For Each r In ActiveSheet.UsedRange
If LCase(r.Value) Like "*joint*" Then
r.Interior.ColorIndex = 37
End If
Next
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=4, Criteria1:="=*joint*", Operator:=xlAnd
End Sub
Sub Hole()
Dim r As Range
For Each r In ActiveSheet.UsedRange
If LCase(r.Value) Like "*hole*" Then
r.Interior.ColorIndex = 16
End If
Next
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=5, Criteria1:="=*hole*", Operator:=xlAnd
End Sub
Sub Fastener()
Dim r As Range
For Each r In ActiveSheet.UsedRange
If LCase(r.Value) Like "*Fastener*" Then
r.EntireRow.Interior.ColorIndex = 33
End If
Next
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=5, Criteria1:="=*Fastener*", Operator:=xlAnd
End Sub
Any help is appreciated.
|