Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lower case letter "i" always converts to upper case | Excel Discussion (Misc queries) | |||
Converting "uppercase" string data to "lower case" in CSV file | Excel Discussion (Misc queries) | |||
VB Script: Code for "Sheets in selection" | Excel Discussion (Misc queries) | |||
how do I count only lower case "x" and exclude upper case "X" | Excel Worksheet Functions | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming |