Hi,

I have cells containing strings of the type "Mumbai - 400078" and
"Belgaum - 590011" with the following pattern:
Alphabetic string followed by space followed by hyphen followed by
space and then a numeric string. I want to fill such cells with a
color (interior.colorindex)? How do I identify such cells to the code?

Thanks in Advance for the help.

Regards,
Raj

didn't give much information but maybe some kind of loop with

With Range("A1")
If InStr(1, .Value, " - ") 1 Then
..Interior.ColorIndex = 19
End If
End With



--


Gary Keramidas
Excel 2003


"Raj" wrote in message
...
Hi,

I have cells containing strings of the type "Mumbai - 400078" and
"Belgaum - 590011" with the following pattern:
Alphabetic string followed by space followed by hyphen followed by
space and then a numeric string. I want to fill such cells with a
color (interior.colorindex)? How do I identify such cells to the code?

Thanks in Advance for the help.

Regards,
Raj

On Sat, 27 Mar 2010 18:14:25 -0700 (PDT), Raj wrote:

Hi,

I have cells containing strings of the type "Mumbai - 400078" and
"Belgaum - 590011" with the following pattern:
Alphabetic string followed by space followed by hyphen followed by
space and then a numeric string. I want to fill such cells with a
color (interior.colorindex)? How do I identify such cells to the code?

Thanks in Advance for the help.

Regards,
Raj

Here's an example of one way of doing this using a regular expression to match
your pattern:

==========================================
Option Explicit
Sub cSpec()
Dim rg As Range, c As Range
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^[A-Z]+\s-\s\d+$"
re.Global = False
re.ignorecase = True
Set rg = Range("A1:A10")
For Each c In rg
If re.test(c) = True Then
c.Interior.ColorIndex = 3
Else
c.Interior.ColorIndex = xlColorIndexNone
End If
Next c
End Sub
=========================================
--ron

If your number string is **always** six digits long, you can do this...

Sub ColorCells()
Dim Cell As Range, YourRange As Range
Set YourRange = Worksheets("Sheet2").Range("A1:C100")
YourRange.Interior.ColorIndex = xlColorIndexNone
For Each Cell In YourRange
If Cell Like "* - ######" Then Cell.Interior.ColorIndex = 3
Next
End Sub

--
Rick (MVP - Excel)



"Raj" wrote in message
...
Hi,

I have cells containing strings of the type "Mumbai - 400078" and
"Belgaum - 590011" with the following pattern:
Alphabetic string followed by space followed by hyphen followed by
space and then a numeric string. I want to fill such cells with a
color (interior.colorindex)? How do I identify such cells to the code?

Thanks in Advance for the help.

Regards,
Raj

Thanks a ton, everyone. I tried Rick's solution and it worked. I will
try out and study Ron's Regex solution next.

Regards,
Raj



On Mar 28, 7:55*am, "Rick Rothstein"
wrote:
If your number string is **always** six digits long, you can do this...

Sub ColorCells()
* Dim Cell As Range, YourRange As Range
* Set YourRange = Worksheets("Sheet2").Range("A1:C100")
* YourRange.Interior.ColorIndex = xlColorIndexNone
* For Each Cell In YourRange
* * If Cell Like "* - ######" Then Cell.Interior.ColorIndex = 3
* Next
End Sub

--
Rick (MVP - Excel)

"Raj" wrote in message

...

Hi,

I have cells containing strings of the type "Mumbai - 400078" and
"Belgaum - 590011" with the following pattern:
Alphabetic string followed by space followed by hyphen followed by
space and then a numeric string. I want to fill such cells with a
color (interior.colorindex)? How do I identify such cells to the code?

Thanks in Advance for the help.

Regards,
Raj

On Sat, 27 Mar 2010 22:55:14 -0400, "Rick Rothstein"
wrote:

If your number string is **always** six digits long, you can do this...

Sub ColorCells()
Dim Cell As Range, YourRange As Range
Set YourRange = Worksheets("Sheet2").Range("A1:C100")
YourRange.Interior.ColorIndex = xlColorIndexNone
For Each Cell In YourRange
If Cell Like "* - ######" Then Cell.Interior.ColorIndex = 3
Next
End Sub

--
Rick (MVP - Excel)

I interpreted the OP to mean by "alphabetic" that that string only contained
letters. Yours will match any character, or even no character, in the first
part of the string.

As a matter of fact, the "*" is superfluous in your pattern.
--ron

On Sun, 28 Mar 2010 06:49:12 -0400, Ron Rosenfeld
wrote:

I interpreted the OP to mean by "alphabetic" that that string only contained
letters.

I should have written "letters in the first part"

--ron

Ron has raised a good point about your original specification and what my
code does to meet it. You said that the part before the space/dash/space
should be alphabetic... my code allows that part of the text being tested to
be any characters, not just letters of the alphabet. The function below will
test the text to see if the first part is, in fact, alphabetic while the
second part is six digits (with both parts delimited by the space/dash/space
characters). So, use this instead of the code I posted earlier...

Sub ColorCells()
Dim Cell As Range, YourRange As Range
Set YourRange = Worksheets("Sheet2").Range("A1:C100")
YourRange.Interior.ColorIndex = xlColorIndexNone
For Each Cell In YourRange
If Not Cell Like "*[!A-Za-z]* - ######" And Cell Like _
"* - ######" Then Cell.Interior.ColorIndex = 3
Next
End Sub

--
Rick (MVP - Excel)



"Raj" wrote in message
...
Thanks a ton, everyone. I tried Rick's solution and it worked. I will
try out and study Ron's Regex solution next.

Regards,
Raj



On Mar 28, 7:55 am, "Rick Rothstein"
wrote:
If your number string is **always** six digits long, you can do this...

Sub ColorCells()
Dim Cell As Range, YourRange As Range
Set YourRange = Worksheets("Sheet2").Range("A1:C100")
YourRange.Interior.ColorIndex = xlColorIndexNone
For Each Cell In YourRange
If Cell Like "* - ######" Then Cell.Interior.ColorIndex = 3
Next
End Sub

--
Rick (MVP - Excel)

"Raj" wrote in message

...

Hi,

I have cells containing strings of the type "Mumbai - 400078" and
"Belgaum - 590011" with the following pattern:
Alphabetic string followed by space followed by hyphen followed by
space and then a numeric string. I want to fill such cells with a
color (interior.colorindex)? How do I identify such cells to the code?

Thanks in Advance for the help.

Regards,
Raj

If your number string is **always** six digits long, you can do this...

Sub ColorCells()
Dim Cell As Range, YourRange As Range
Set YourRange = Worksheets("Sheet2").Range("A1:C100")
YourRange.Interior.ColorIndex = xlColorIndexNone
For Each Cell In YourRange
If Cell Like "* - ######" Then Cell.Interior.ColorIndex = 3
Next
End Sub

I interpreted the OP to mean by "alphabetic" that that string only
contained
letters. Yours will match any character, or even no character, in the
first
part of the string.

As a matter of fact, the "*" is superfluous in your pattern.

Good point Ron! I completely overlooked that. I just posted some corrected
code for the OP (still using the Like operator test method).

Going back to my original code though... what did you mean the "*" is
superfluous in your pattern? If we leave it out, then this test will fail...

If "Mumbai - 400078" like " - ######" Then

Or did I miss the point of your comment?

--
Rick (MVP - Excel)

On Sun, 28 Mar 2010 10:36:21 -0400, "Rick Rothstein"
wrote:

Good point Ron! I completely overlooked that. I just posted some corrected
code for the OP (still using the Like operator test method).

Going back to my original code though... what did you mean the "*" is
superfluous in your pattern? If we leave it out, then this test will fail...

If "Mumbai - 400078" like " - ######" Then

Or did I miss the point of your comment?

--
Rick (MVP - Excel)

I was not clear (in either my writing or my own mind :-)

I was trying to express that your original code, as well as your current code,
will match a string with zero characters prior to the <space<hyphen<space

In other words, a string: ' - 123456' will match (in both of your code
examples).

Also, and I don't know how this may apply to the OP's problem, your code allows
a first string of any length, including zero, and the second string must be
exactly six digits (which you did specify in your initial post).

In mine, both strings must be at least 1 character, with no upper limit. But
it would be trivial to add more restrictive length parameters. For example, if
six digits were required at the end, then:

re.Pattern = "^[A-Z]+\s-\s\d+$"

gets changed to

re.Pattern = "^[A-Z]+\s-\s\d{6}$"

--ron

One more change in response to a comment by Ron. It seems that my code will
allow no characters to appear before the space/dash/space and that is should
require at least one character (or more) to be in that position. Ron raises
a good point, so here is the adjusted code to cater to this requirement as
well...

Sub ColorCells()
Dim Cell As Range, YourRange As Range
Set YourRange = Worksheets("Sheet2").Range("A1:C100")
YourRange.Interior.ColorIndex = xlColorIndexNone
For Each Cell In YourRange
If Not Cell Like "*[!A-Za-z]* - ######" And Cell Like "* - ######" _
And Cell Like "?* - *" Then Cell.Interior.ColorIndex = 3
Next
End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Ron has raised a good point about your original specification and what my
code does to meet it. You said that the part before the space/dash/space
should be alphabetic... my code allows that part of the text being tested
to be any characters, not just letters of the alphabet. The function below
will test the text to see if the first part is, in fact, alphabetic while
the second part is six digits (with both parts delimited by the
space/dash/space characters). So, use this instead of the code I posted
earlier...

Sub ColorCells()
Dim Cell As Range, YourRange As Range
Set YourRange = Worksheets("Sheet2").Range("A1:C100")
YourRange.Interior.ColorIndex = xlColorIndexNone
For Each Cell In YourRange
If Not Cell Like "*[!A-Za-z]* - ######" And Cell Like _
"* - ######" Then Cell.Interior.ColorIndex = 3
Next
End Sub

--
Rick (MVP - Excel)



"Raj" wrote in message
...
Thanks a ton, everyone. I tried Rick's solution and it worked. I will
try out and study Ron's Regex solution next.

Regards,
Raj



On Mar 28, 7:55 am, "Rick Rothstein"
wrote:
If your number string is **always** six digits long, you can do this...

Sub ColorCells()
Dim Cell As Range, YourRange As Range
Set YourRange = Worksheets("Sheet2").Range("A1:C100")
YourRange.Interior.ColorIndex = xlColorIndexNone
For Each Cell In YourRange
If Cell Like "* - ######" Then Cell.Interior.ColorIndex = 3
Next
End Sub

--
Rick (MVP - Excel)

"Raj" wrote in message

...

Hi,

I have cells containing strings of the type "Mumbai - 400078" and
"Belgaum - 590011" with the following pattern:
Alphabetic string followed by space followed by hyphen followed by
space and then a numeric string. I want to fill such cells with a
color (interior.colorindex)? How do I identify such cells to the code?

Thanks in Advance for the help.

Regards,
Raj

Good point Ron! I completely overlooked that. I just posted some corrected
code for the OP (still using the Like operator test method).

Going back to my original code though... what did you mean the "*" is
superfluous in your pattern? If we leave it out, then this test will
fail...

If "Mumbai - 400078" like " - ######" Then

Or did I miss the point of your comment?

I was not clear (in either my writing or my own mind :-)

I was trying to express that your original code, as well as your current
code,
will match a string with zero characters prior to the
<space<hyphen<space

In other words, a string: ' - 123456' will match (in both of your code
examples).

Also, and I don't know how this may apply to the OP's problem, your code
allows
a first string of any length, including zero, and the second string must
be
exactly six digits (which you did specify in your initial post).

That's a reasonable observation... thanks for noting it. I just posted
revised code to make sure at least one character exists before the
space/dash/space.

--
Rick (MVP - Excel)

On Sun, 28 Mar 2010 12:47:20 -0400, "Rick Rothstein"
wrote:

That's a reasonable observation... thanks for noting it. I just posted
revised code to make sure at least one character exists before the
space/dash/space.

I noted your last revision. Of course, these kinds of modifications seem
simpler to me with regular expressions, than with sequential Like statements.
But that's just a matter of taste.
--ron