VBA: Highlight values between if don't contains "."

Hello Experts,

I have in one sheet the following values in Column A

Argentina
Argentina.50158
Argentina.50189.AR020010
Australia
Australia.50498
Australia.50498.AU018017


I need to highlight the lines of values in column A which doesn't contains "."

In that case Argentina and Australia lines (1 and 4 need to be highlighted).

Thank you very much for your help.
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Go to Conditional formatting..Apply this formula...

=AND(ISERROR(SEARCH(".",A1,1)),A1<>"")

Open in new window


Enclosed your formula for your workbook...

Saurabh...
Cf.xlsx
0
LD16Author Commented:
Thank you, is there a way to have this verion in VBA?
0
Saurabh Singh TeotiaCommented:
You can use the following code which will do what you are looking for...

Sub hvalues()

    Dim rng As Range, cell As Range
    Dim lrow As Long

    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Set rng = Range("A1:A" & lrow)

    For Each cell In rng


        If InStr(1, cell.Value, ".", vbTextCompare) = 0 Then cell.Interior.ColorIndex = 3

    Next cell

End Sub

Open in new window


Again this will highlight values in red in A Column from Row-1 till last row...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LD16Author Commented:
Perfect,

Thank you very much it works!

I have a last question what about if I want to highlight the values related to the third level which means:

Argentina.50189.AR020010

That means string which have two "." "."
0
Saurabh Singh TeotiaCommented:
In that case assuming you just want to highlight double "." then use...

Sub hvalues()

    Dim rng As Range, cell As Range
    Dim lrow As Long

    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Set rng = Range("A1:A" & lrow)

    For Each cell In rng


        If InStr(1, cell.Value, ".", vbTextCompare) > 0 And InStr(InStr(1, cell.Value, ".", vbTextCompare) + 1, cell.Value, ".", vbTextCompare) > 0 Then cell.Interior.ColorIndex = 3

    Next cell

End Sub

Open in new window


Saurabh...
0
Rory ArchibaldCommented:
Not for points:

You could simplify the test to:

If Len(cell.Value) - Len(Replace(cell.Value, ".", vbNullString)) > 1 Then cell.Interior.ColorIndex = 3

Open in new window

0
Saurabh Singh TeotiaCommented:
And If you want to check for 2 dots..then rory one will be...

If Len(cell.Value) - Len(Replace(cell.Value, ".", vbNullString)) >=2 Then cell.Interior.ColorIndex = 3

Open in new window


This will specifically will check if you have 2 or more..
0
Rory ArchibaldCommented:
>1 is checking for 2...
:)
0
Saurabh Singh TeotiaCommented:
Rory,

 He want to specifically check for 2 dots and highlight those in his later on request.. so for that you need to be >=2 ... ;-)

Saurabh...
0
Rory ArchibaldCommented:
Um, no. If you want specifically 2 dots, you need =2. If you want at least 2 dots, >1 and >=2 do the same thing...
0
Saurabh Singh TeotiaCommented:
Yeah i thought about that but i was like not sure what he is looking for and yeah now i notice you don't have a equal to sign their... my bad..starting my day right now so my processor is still not fully 100% upto speed.. :-)
0
LD16Author Commented:
Hello,  Experts,

Thank you very much both for your help!
0
Rory ArchibaldCommented:
You're welcome but I did specifically say that my comments weren't for points! I really think all the points should go to Saurabh, please.
0
LD16Author Commented:
Ok, thank you Rory, for your feedback. I rill request to reopen the question.
0
Rory ArchibaldCommented:
Thanks. :)
0
ProfessorJimJamCommented:
Rory,

i liked very much the simplicity of that Len - Len method.  


Great place to learn invaluable stuff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.