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