Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 33
Default Fix Code: Select Case and "Contains" selection

Hello: Here's my code. I want to look for anything that contains the
following values: "Option1" and "Option2". Sometimes it will be the single
value in the cell, somtimes it will be part of a string. I have tried
numerous syntaxes, but am coming up empty. HELP~

Dim Mark As Range

For Each Mark In Union([D6:F1200], [AF6:AF1200]).Cells

Select Case Mark.Value
Case "Option1"
Mark.Font.ColorIndex = 55
Mark.Font.Bold = True
Case "Option2"
Mark.Font.ColorIndex = 55
Mark.Font.Bold = True
Case Else
Mark.Font.Color = vbBlack
Mark.Font.Bold = False
End Select
Next
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 983
Default Fix Code: Select Case and "Contains" selection

I would be much more inclined to use Find and FindNext than your current
method. Set a range object equal to the union of the two ranges (very sismlar
to what you have now. Format that range similar to your case else. Then do
the Find and FindNext looking at xlPart. This should be a whole pile faster
and will get around the problem with the "part of a string". Just remember
that find next will loop infinitely so you need to set up a stop that checks
to make sure that you have not come back to the original address where you
started. Also make sure that you find something in the first place using a
range object that may be set to nothing by the original find. If you need
help with the code just ask...

HTH

"Bettergains" wrote:

Hello: Here's my code. I want to look for anything that contains the
following values: "Option1" and "Option2". Sometimes it will be the single
value in the cell, somtimes it will be part of a string. I have tried
numerous syntaxes, but am coming up empty. HELP~

Dim Mark As Range

For Each Mark In Union([D6:F1200], [AF6:AF1200]).Cells

Select Case Mark.Value
Case "Option1"
Mark.Font.ColorIndex = 55
Mark.Font.Bold = True
Case "Option2"
Mark.Font.ColorIndex = 55
Mark.Font.Bold = True
Case Else
Mark.Font.Color = vbBlack
Mark.Font.Bold = False
End Select
Next

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default Fix Code: Select Case and "Contains" selection

Dim Mark As Range

For Each Mark In Union([D6:F1200], [AF6:AF1200]).Cells

With Mark
.Font.Color = vbBlack
.Font.Bold = False
Select Case True
Case .Value Like "*Option1*" Or _
.Value Like "*Option2*"
.Font.ColorIndex = 55
.Font.Bold = True
Case Else
End Select
End With
Next Mark

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bettergains" wrote in message
...
Hello: Here's my code. I want to look for anything that contains the
following values: "Option1" and "Option2". Sometimes it will be the

single
value in the cell, somtimes it will be part of a string. I have tried
numerous syntaxes, but am coming up empty. HELP~

Dim Mark As Range

For Each Mark In Union([D6:F1200], [AF6:AF1200]).Cells

Select Case Mark.Value
Case "Option1"
Mark.Font.ColorIndex = 55
Mark.Font.Bold = True
Case "Option2"
Mark.Font.ColorIndex = 55
Mark.Font.Bold = True
Case Else
Mark.Font.Color = vbBlack
Mark.Font.Bold = False
End Select
Next



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 33
Default Fix Code: Select Case and "Contains" selection

Thanks, Bob. that is the syntax I was looking for.

Also, can 3 options be used with Case Selects? I've edited the code below to
show what I am referring to. Obviously, the edited syntax does NOT work.

"Bob Phillips" wrote:

Dim Mark As Range

For Each Mark In Union([D6:F1200], [AF6:AF1200]).Cells

With Mark
.Font.Color = vbBlack
.Font.Bold = False
Select Case True
Case .Value Like "*Option1*" Or _
.Value Like "*Option2*" or _

.value like "*Option3*"
.Font.ColorIndex = 55
.Font.Bold = True
Case Else
End Select
End With
Next Mark

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bettergains" wrote in message
...
Hello: Here's my code. I want to look for anything that contains the
following values: "Option1" and "Option2". Sometimes it will be the

single
value in the cell, somtimes it will be part of a string. I have tried
numerous syntaxes, but am coming up empty. HELP~

Dim Mark As Range

For Each Mark In Union([D6:F1200], [AF6:AF1200]).Cells

Select Case Mark.Value
Case "Option1"
Mark.Font.ColorIndex = 55
Mark.Font.Bold = True
Case "Option2"
Mark.Font.ColorIndex = 55
Mark.Font.Bold = True
Case Else
Mark.Font.Color = vbBlack
Mark.Font.Bold = False
End Select
Next




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default Fix Code: Select Case and "Contains" selection

Yes, more options are okay.

What do you mean that it doesn't work, it seems to work okay for me?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bettergains" wrote in message
...
Thanks, Bob. that is the syntax I was looking for.

Also, can 3 options be used with Case Selects? I've edited the code below

to
show what I am referring to. Obviously, the edited syntax does NOT work.

"Bob Phillips" wrote:

Dim Mark As Range

For Each Mark In Union([D6:F1200], [AF6:AF1200]).Cells

With Mark
.Font.Color = vbBlack
.Font.Bold = False
Select Case True
Case .Value Like "*Option1*" Or _
.Value Like "*Option2*" or _

.value like "*Option3*"
.Font.ColorIndex = 55
.Font.Bold = True
Case Else
End Select
End With
Next Mark

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bettergains" wrote in message
...
Hello: Here's my code. I want to look for anything that contains the
following values: "Option1" and "Option2". Sometimes it will be the

single
value in the cell, somtimes it will be part of a string. I have tried
numerous syntaxes, but am coming up empty. HELP~

Dim Mark As Range

For Each Mark In Union([D6:F1200], [AF6:AF1200]).Cells

Select Case Mark.Value
Case "Option1"
Mark.Font.ColorIndex = 55
Mark.Font.Bold = True
Case "Option2"
Mark.Font.ColorIndex = 55
Mark.Font.Bold = True
Case Else
Mark.Font.Color = vbBlack
Mark.Font.Bold = False
End Select
Next








  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 33
Default Fix Code: Select Case and "Contains" selection

Yep, it works. Something was missing earlier and I overlooked it.

"Bob Phillips" wrote:

Yes, more options are okay.

What do you mean that it doesn't work, it seems to work okay for me?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bettergains" wrote in message
...
Thanks, Bob. that is the syntax I was looking for.

Also, can 3 options be used with Case Selects? I've edited the code below

to
show what I am referring to. Obviously, the edited syntax does NOT work.

"Bob Phillips" wrote:

Dim Mark As Range

For Each Mark In Union([D6:F1200], [AF6:AF1200]).Cells

With Mark
.Font.Color = vbBlack
.Font.Bold = False
Select Case True
Case .Value Like "*Option1*" Or _
.Value Like "*Option2*" or _

.value like "*Option3*"
.Font.ColorIndex = 55
.Font.Bold = True
Case Else
End Select
End With
Next Mark

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bettergains" wrote in message
...
Hello: Here's my code. I want to look for anything that contains the
following values: "Option1" and "Option2". Sometimes it will be the
single
value in the cell, somtimes it will be part of a string. I have tried
numerous syntaxes, but am coming up empty. HELP~

Dim Mark As Range

For Each Mark In Union([D6:F1200], [AF6:AF1200]).Cells

Select Case Mark.Value
Case "Option1"
Mark.Font.ColorIndex = 55
Mark.Font.Bold = True
Case "Option2"
Mark.Font.ColorIndex = 55
Mark.Font.Bold = True
Case Else
Mark.Font.Color = vbBlack
Mark.Font.Bold = False
End Select
Next






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
lower case letter "i" always converts to upper case Carolyn Excel Discussion (Misc queries) 1 August 28th 08 01:56 AM
Converting "uppercase" string data to "lower case" in CSV file [email protected] Excel Discussion (Misc queries) 2 August 12th 08 08:36 PM
VB Script: Code for "Sheets in selection" J@Y Excel Discussion (Misc queries) 1 February 9th 07 09:52 PM
how do I count only lower case "x" and exclude upper case "X" jbeletz Excel Worksheet Functions 3 October 14th 06 10:50 PM
Using "Cells" to write "Range("A:A,H:H").Select" Trip Ives[_2_] Excel Programming 3 June 5th 04 03:13 PM


All times are GMT +1. The time now is 08:32 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"