asked on

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.

Saurabh Singh Teotia

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

Luis Diaz

ASKER

Thank you, is there a way to have this verion in VBA?

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

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 "." "."

Saurabh Singh Teotia

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...

Rory Archibald

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

Saurabh Singh Teotia

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..

Rory Archibald

>1 is checking for 2...
:)

Saurabh Singh Teotia

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...

Rory Archibald

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...

Saurabh Singh Teotia

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.. :-)

Luis Diaz

ASKER

Hello, Experts,

Thank you very much both for your help!

Rory Archibald

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.

Luis Diaz

ASKER

Ok, thank you Rory, for your feedback. I rill request to reopen the question.

Rory Archibald

Thanks. :)

Professor J

Rory,

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

Great place to learn invaluable stuff

VBA: Highlight values between if don't contains &quot;.&quot;

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