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

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

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

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

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

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