Highlight cells in a certain column that contain 2 numbers

[Kennethc]

Hi all

I'm looking to highlight any cells in a given column (Address Line 1) if the cell contains 2 numbers.

Sample data:

Address Line 1

23 Ash Road
12 Cedar Avenue
Flat 2 14 Elm Street
Flat 6

In the above column, Flat 2 14 Elm Street would be highlighted with a green fill as it has 2 numbers in the cell.

Any assistance is greatly appreciated.

Kenneth

[Gatti]

Hello... I'm pretty sure it can be done easily... but couldn't find a way... so:

Please Login or Register  to view this content.

[Kennethc]

Hi

Yes that worked for me. In the interest of learning and understanding what you did would you be so kind and give me a brief explanation of what's happening, please?

Thanks

[Gatti]

@Kennethc

Glad it works!

The best way to understand the code is DEBUG it, pressing F8 and checking the how TmpValue changes on each line

What I did is:

Defined the Worksheet I'm going to check
Defined the Range with the data I'm checking (A1:A last row with data)
For each cell in this range i'm doing a loop, that does:
Get the cell value and set it into "TmpValue"
TmpValue replaces all letters, one by one, for ""
so, the only thing remains are the numbers and space between numbers

If InStr(TmpValue, " ") <> 0 Then

InStr is like a Find Formula in VBA

I'm trying to find " " (one space) in TmpValue... It'd only happen if there is a space between 2 or more numbers, cause I've used Trim before.
the result 0 means it didn't find anything... so if it is different then 0, do the routine

Hope it is clear... I'm brazilian and my english isn't very good

[jindon]

Select the range and run the code

Please Login or Register  to view this content.

[Gatti]

Much better!!!
Thanks jindon

[Gatti]

By the way... Just made another way to do it, using SPLIT:

Please Login or Register  to view this content.

[Kennethc]

Hi Gatti

How can I amend your code so that it will look on the active sheet for a particular column heading in row 1 called say, "Address1"?
Your code worked brilliantly but only in column A of a sheet called MySheet. The data I need it to work on could be in any column letter but will always be called "Address1".

I hope you don't mind me asking, thanks.

Kenneth