Highlight Cell If

[billandrew]

Good morning

Is there a way to highlight a cell if there are more than 2 letters following the number in a string.

Before

101po

A 1002pore

1003ro Next

1004pos End

A 1006po

1002pow

<tbody>
</tbody>

After

1001po

A 1002pore

1003ro Next

1004pos End

A 1006po

1002pow

<tbody>
</tbody>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG19Jun35
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Sp [COLOR="Navy"]In[/COLOR] Split(Dn.Value, " ")
        [COLOR="Navy"]If[/COLOR] Sp Like "####[A-Za-z][A-Za-z][A-Za-z]*" [COLOR="Navy"]Then[/COLOR]
            Dn.Font.Bold = True
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Sp
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[billandrew]

Works great...

 

[billandrew]

If the preceding digits to the number is only 2 or 3, should i amend like "###[A-Za-z][A-Za-z][A-Za-z]*" or "##[A-Za-z][A-Za-z][A-Za-z]*"

 

[MickG]

You're welcome
NB:- Yes change the hashes "#" count to the number you require required

 

[billandrew]

What does the Split actually do, if I may..

 

[billandrew]

If you can, also looking to highlight cells where there is

1. no characters following the numeral
2. One character following the numeral.

 

[billandrew]

Below is an example of the previous request

1. There are no characters between the numeral & the space
2. One character between the numeral & the space

Before	After
A 101 End	A 101 End
B 1002s Po	B 1002s Po

<tbody>
</tbody>

Thank You

 

[Rick Rothstein]

Does this macro do what you want...

[table="width: 500"]
[tr]
	[td]Sub HighlightIfNotTwoLetters()
  Dim Dn As Range
  For Each Dn In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    Dn.Font.Bold = Not " " & Dn & " " Like "*####[A-Za-z][A-Za-z] *"
  Next
End Sub[/td]
[/tr]
[/table]

 

[billandrew]

Works perfect. If I needed to highlight the cells if one character between the numeral & the space how could this be achieved?