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>

 

[Rick Rothstein]

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

As far as I know, the code I posted already does this. Are you saying it doesn't? If so, can you post an example that did not get highlighted?

 

[billandrew]

Yes you are correct, however the code (no fault of you) highlights cells I do not require to be highlighted. Post #6 - If only the bottom example can be highlighted, this would be great.

cells where there is only one character following the numeral.

Thank You sir

 

[Rick Rothstein]

Yes you are correct, however the code (no fault of you) highlights cells I do not require to be highlighted. Post [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=6]#6 [/URL] - If only the bottom example can be highlighted, this would be great.

Did you mean Post #8 (instead of #6 )? If so, are you then saying that...

A 101 End

should not be highlighted?

 

[billandrew]

Yes Mr. Rothstein you are correct on both points.

Thank You

 

[Rick Rothstein]

Yes Mr. Rothstein you are correct on both points.

Point of clarification.... the number in the example we were talking about (A 101 End) only has 3 digits, was that a mistake, should it have been a 4-digit number? If not, is it not being highlighted because it only has 3 digits or because it is a number without a letter after it? In other words, will the number we examine for highlighting always have 4 digits (like my previous code assumed)?

 

[billandrew]

It is not being highlighted because it does not have a letter following the numeral.

The cell may have 3 digits, 4 digits or even 2 digits.

 

[Rick Rothstein]

The cell may have 3 digits, 4 digits or even 2 digits.

I am still a little unclear about your requirements. I was under the impression the code should test only when there were 4 digits. Are you saying these should be highlighted...

B 12s Po

123pos End

12pow

 

[billandrew]

the below refers to what I am trying to explain.

2 digits, 3 digits, 4 digits.

If there is one character following the numerals highlight

B 12s Po
B 122S Po
B 1222s po

<tbody>
</tbody>

 

[Rick Rothstein]

the below refers to what I am trying to explain.

2 digits, 3 digits, 4 digits.

If there is one character following the numerals highlight

B 12s Po
B 122S Po
B 1222s po

<tbody>
</tbody>

In Message #10 you said that the code I posted in Message #9 "worked perfectly"... apparently, it doesn't as it test only for 4 digit numbers. Here is my code, modified to account for all the above comments (hopefully)... let me know how it works.

[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] *" And Not Dn & " " Like "*## *"
  Next
End Sub[/td]
[/tr]
[/table]

 

[billandrew]

This works as described. Perfect. Thank You.


I've uploaded additional data to the ws. There are strings mixed in with no numerals in between which are highlighted, Is there a way to not highlight these.

example..

A PO
B PO
A Purchase