Home Menu

Menu


ExcelBanter » Excel Newsgroups » New Users to Excel > Colored and filtered cells
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 60
Default Colored and filtered cells
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 With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 35,218
Default Colored and filtered cells
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



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.

--

Dave Peterson
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 22,906
Default 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.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 60
Default Colored and filtered cells
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.

"Dave Peterson" wrote:

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



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.

--

Dave Peterson

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 60
Default Colored and filtered cells
Thanks Gord! That did it.

"Gord Dibben" wrote:

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.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
sum colored cells AOP Excel Discussion (Misc queries) 4 November 8th 07 07:25 PM
counting colored cells James P Excel Discussion (Misc queries) 2 June 14th 06 05:39 PM
Select only colored cells BBlue Excel Worksheet Functions 2 March 23rd 06 09:15 PM
What formula can I use to sum only certain colored cells in an exc Favi Excel Worksheet Functions 1 February 7th 06 01:27 AM
Count or sum colored cells brightgirl Excel Worksheet Functions 2 December 7th 04 03:34 PM


All times are GMT +1. The time now is 07:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement