string search in a range

[nicitalia]

Hi all folks,

this time I've a problem related to the research of a some kind of string in a range, here it is:

in column A I've got 10.000 cells with this kind of values (the format of the cell is Text):
A1 = 10.1
A2 = 10.11
A3 = 10.12.0
A4 = 10.12.00

I want to fill all the cells that have the type of value of A3, so a string with 7 charaters at all!

Sub ColoraCelle()

For Each c In Sheet(1).Column(A)
if ...then...c.interior.clorindex=3
next
End Sub

thank you in advance!

bye bye

 

[SkipVought]

Hi,

Your English is not making sense.

You have 10 cells (trailing zeros make no sense for a count)

so for each cell in the column that has a STRUCTURE like nn.nn.n, you want the interior color to be RED. YES?

Or for each cell in the column that has a VALUE =10.12.0, you want the interior color to be RED. Or WHAT?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[nicitalia]

Hi,

I've done it myself.

Sub ColoraCelle()

Dim Zona As Range
Set Zona = Sheets(1).Range([A1], [A1].End(xlDown))
For Each c In Zona
If Len(c.Value) = 7 Then c.Offset(0, 1).Interior.ColorIndex = 28
Next
End Sub

Thank you

goodbye

 

[SkipVought]

Well then the structure or form of the string means NOTHING.

The LENGTH is the ONLY criteria!

Why did you not state that up front, rather than all these other non-essential and irrelevent elements?

Sub ColoraCelle()
For Each c In Sheets(1).Range([A1], [A1].End(xlDown))
  with c
    If Len(.Value) = 7 Then .Offset(0, 1).Interior.ColorIndex = 28
  end with
Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

For the problem as originally described:

Sub ColoraCelle()
Dim Cel As Range
For Each Cel In Sheets(1).Range([A1], [A1].End(xlDown))
  With Cel
    If .Text Like "##.##.#" Then .Interior.ColorIndex = 3
  End With
Next
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[zelgar]

Sounds like use of Conditional Formatting would be a lot easier than writing a macro