Colored and filtered cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tony S. - 09 Jan 2008 21:40 GMT

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.

Reply to this Message

Dave Peterson - 09 Jan 2008 23:16 GMT

You're comparing lower case stuff to "*Fastener*"

That uppercase F is going to screw you up!

If LCase(r.Value) Like "*fastener*" Then
or if you're lazy:
If LCase(r.Value) Like lcase("*Fastener*") Then

> 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
[quoted text clipped - 47 lines]
>
> Any help is appreciated.

Signature

Dave Peterson

Reply to this Message

Tony S. - 09 Jan 2008 23:51 GMT

Thanks Dave.

I had no idea that the formula was case sensitive. I originally copied and
modified it from another post. Thats the difference between a pro, like
youself, and a hacker like me. I appreciate you help and prompt response.

> You're comparing lower case stuff to "*Fastener*"
>
[quoted text clipped - 55 lines]
> >
> > Any help is appreciated.

Reply to this Message

Gord Dibben - 09 Jan 2008 23:37 GMT

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

>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
[quoted text clipped - 47 lines]
>
>Any help is appreciated.

Reply to this Message

Tony S. - 09 Jan 2008 23:53 GMT

Thanks Gord! That did it.

> Change the upper case "F" in "Fastener to a lower case "f"
>
[quoted text clipped - 53 lines]
> >
> >Any help is appreciated.

Reply to this Message