Row Color based on Case

[Blessy Clara]

Hi,

Could someone render some suggestions, After searching through many codes and modifications I am no where close to the requirement.

Data to be worked on

Before Macro

Column A

1 AALTO SCIENTIFIC, LTD.
Add: 1959 Kellogg Ave. Carlsbad, CA 92008
E-mail: kjaalto@msn.com
Web site: www.aaltoscientific.com
2 AASTON, INC.
Add: 12 Falmouth Rd. Weiiesley, MA 02481
E-mail: aaston@aol.com
President: Randolph T. Hatch, Ph.D.
R&D expertise: Research, development,
production and marketing of specialty protein
products.
Technological expertise: RD, F, BA, Q, E,
3 AASTROM BIOSCIENCES, INC.
Add: 24 Frank Lloyd Wright Dr., P.O. Box 376,
Ann Arbor, MI 48106
E-mail: mail@aastrom.com
Web site: www.aastrom.com
President/CEO: R. Douglas Armstrong, Ph.D.
------------------------------------
Modified version of code from online search
< Sub test()
Dim rng As Range, c As Range
Set rng = Range(Range("A1"), Range("A1").End(xlDown))
For Each c In ActiveSheet.UsedRange
c.Interior.ColorIndex = xlNone
If IsNumeric(Left(c, 1)) And UCase(Left(c, 1)) = Left(c, 1) Then c.Interior.ColorIndex = 6
Next c
End Sub>

The above code converts all the cells that contains Numeric values. But the requirement is
If cell value begins with numeric and then followed by Text in Uppercase then the color of the cell has to be changed

Example (Only those highlighted in red)
1 AALTO SCIENTIFIC, LTD.
2 AASTON, INC.
3 AASTROM BIOSCIENCES, INC.

Goes until 3000 + Names as this

Macro to consider on those rows highlighted in red (here)

1 AALTO SCIENTIFIC, LTD.
Add: 1959 Kellogg Ave. Carlsbad, CA
92008
E-mail: kjaalto@msn.com
Web site: www.aaltoscientific.com
2 AASTON, INC.
Add: 12 Falmouth Rd. Weiiesley, MA 02481
E-mail: aaston@aol.com
President: Randolph T. Hatch, Ph.D.
R&D expertise: Research, development,
production and marketing of specialty protein
products.
Technological expertise: RD, F, BA, Q, E,
3 AASTROM BIOSCIENCES, INC.
Add: 24 Frank Lloyd Wright Dr., P.O. Box 376,
Ann Arbor,
MI 48106
E-mail: mail@aastrom.com
Web site: www.aastrom.com
President/CEO: R. Douglas Armstrong, Ph.D.
------------------------
Coz this is the only criteria that will be helpful for me to distinguish between different organizations

 

[mikerickson]

You could use Conditional Formatting with the formula =EXACT(A1,UPPER(A1))

 

[Blessy Clara]

Hi mikerickson

Thank you very much for the response, and the suggestion.

Yes I had already tried conditional formatting formula too. resorting to this method only gave me results where all the cells that has Uppercase or Numbers considered

There are many many cells (I mean - Rows in Column A) that contain only numbers, only Uppercase, or combination of both.
Example
92008
MI 48106
USA
I don't want all of these to be color changed.

Since the only differentiating pattern to identify various organization here is
"Numeric Value followed by a space and Uppercase Letters" if this condition is true then the cell.interior = Some color
Example (Only those highlighted in red)
1 AALTO SCIENTIFIC, LTD.
2 AASTON, INC.
3 AASTROM BIOSCIENCES, INC.

Any suggestions would be very helpful

Thank you

 

[mikerickson]

How about
=AND(ISNUMBER(0+TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",255)),255))), EXACT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),255,255)),UPPER(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),255,255)))))

 

[Blessy Clara]

Hi mikerickson

Thank you for the formula, it was very helpful .