I have several thousand address' in this format:

123 Any Street # 23
234 Main Street # A

I need to have a space after the #. In some cases I do not:

576 South Street #24

Using VBA how can I search a range (1 column) and find the address'
without the space and then mark the cell color red.

Thanks for your help!!
Fred

Hi Fred

After they are marked red what do you intend to do ?

Could you not use the Find/Replace function within Excel if what you are
doing is going to add a space to those cells maked red?

"Fred" wrote:

I have several thousand address' in this format:

123 Any Street # 23
234 Main Street # A

I need to have a space after the #. In some cases I do not:

576 South Street #24

Using VBA how can I search a range (1 column) and find the address'
without the space and then mark the cell color red.

Thanks for your help!!
Fred

On Jul 24, 10:32 am, steve_doc
wrote:
Hi Fred

After they are marked red what do you intend to do ?

Could you not use the Find/Replace function within Excel if what you are
doing is going to add a space to those cells maked red?



"Fred" wrote:
I have several thousand address' in this format:

123 Any Street # 23
234 Main Street # A

I need to have a space after the #. In some cases I do not:

576 South Street #24

Using VBA how can I search a range (1 column) and find the address'
without the space and then mark the cell color red.

Thanks for your help!!
Fred- Hide quoted text -

- Show quoted text -

Hi, I plan to send the Excel sheet to the data entry people to use as
a reference to fix the errors in the database.

Thanks!

Don't bother with VBA, use conditional formatting.

Select column A
Menu FormatConditional Formatting
Change Condition 1 To formula Is
Add a formula of =AND(ISNUMBER(FIND("#",A1)),NOT(ISNUMBER(FIND("# ",A1))))
Click Format
Select Pattern
Choose Red
OK
OK


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Fred" wrote in message
ps.com...
I have several thousand address' in this format:

123 Any Street # 23
234 Main Street # A

I need to have a space after the #. In some cases I do not:

576 South Street #24

Using VBA how can I search a range (1 column) and find the address'
without the space and then mark the cell color red.

Thanks for your help!!
Fred