Seek help to highlight number.

[apple]

Hi,

Please do show me the way to highlight 5 number in front and 5 number at the back. For example the number i want is 12345-ABC-67890.I want to highlight all the number that is 5 number in front, middle got ABC and at the back is 5 number.

Besides that i want highlight the sequence number which is start from 1 to 1.1, 1.1.1, 1.1.2 and so on. After that start loop 2 to 2.2.1,2.2.2, 2.2.3 and so on.

Below is the example:

1.1.0 12345-ABC-647891
1.1.2 67891-DEG-112345
1.1.3 78910-HIJ-123467

2.1.0 65478-KLM-645789
2.2.1 45678-OPQ-489453


I would like to highlight the number follow the sequence for number 1.1.0 to the end 1.1.3 that have 5 number in front, middle ABC and 5 number at the back. After that start loop 2.1.0 until end of 2.2.1
I am new to VBA. Can anyone help me with example? Thanks. Hope to hear any friend to help me soon.

Regards,
Apple

[JimmyTheHand]

Hi,

So you want something like this?

1.1.0 12345-ABC-647891 --> 1.1.0 12345-ABC-647891

If not, then can you show me an example?

2nd question: You want this for all rows? Where are these rows, anyway?

Jimmy

[apple]

Hi Jimmy,

Yes, you are right. I want 1.1.0 12345-ABC-647891, all the rows. This rows are from excel sheet. Hope to hear from you soon.

[apple]

Hi Jimmy,

Any update for my request. Thanks

[JimmyTheHand]

Hi,

try the macro below.

[vba]Sub HighLight()
Dim Rng As Range, c As Range
Dim L As Long, S As String
Set Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each c In Rng.Cells
If c <> "" Then
S = c.Value
With c.Characters(1, InStr(S, "-") - 1).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
With c.Characters(InStrRev(S, "-") + 2).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
End If
Next
End Sub[/vba] Please note the red bold line.
This line defines the cell range that contains these serial (?) numbers. Since I don't know the layout of your excel sheet, I assumed that the numbers are in column A, starting with A1, and there is no other data in column A, only these numbers.

If you have them elsewhere, e.g. on a fixed cell range, or want to run the macro on a range previously selected by user, etc, we can arrange that as well, but I need to know, where to search for these numbers.

Jimmy

[apple]

Hi Jimmy,

Thanks for your respond. But i need the number for example
12345-ABC-67891. That means 5 number in front and at the back and the middle have ABC alphabet. If the looping see that number then it will highlight. Can you advise me for this problem? Thanks

[JimmyTheHand]

Please post a sample workbook.
Put the numbers into column A, and put the desired result into column B.

JImmy

[apple]

Hi Jimmy,

I already attach for you the example. Please see as below:

1.1 12345-ABC-67891 *Must have type A
- i want to auto highlight when press button highlight then highlight 5 number in front, ABC alphabet in middle and 5 number at the back.
Type A. Then highlight OR
1.1.3 12356-ABC-52345 number in front and middle ABC also type A.

Please refer the attachment in yellow colour continue loop until end of 1.

Then continue find in green colour
*Must have type B
1.1.4 12346-DEF
- 5 number in front and middle must have DEF, type B OR

1.1.9 45689-DEF-45612
-I also need 5 number in front, middle 3 alphabet DEF and 5 number at the back. Type B

Jimmy, hope to see your reply soon. Thanks

[JimmyTheHand]

I'm sorry, I still don't get it. I spent 40 minutes on trying to understand what you want to highlight, and what do you mean by "highlight", in the first place, but I had no success.

Try to explain yourself better, otherwise I can do nothing for you. I see that there are 3 columns here, so why don't you put the numbers into columns D, E and F, highlighted as you want them, so that I can see your meaning.

Jimmy

[apple]

Hi Jimmy

Basically the attachment that i send to is the answer that i want to do in coding. There are 2 type highlight that i want to do.

1. Firstly the yellow colour is the one i want to highlight 5 number in front, middle ABC and 5 number at the back, plus the criteria type is A

2. Secondly the green colour is the one i want highlight 5 number in front middle DEF and at the back 5 number plus the criteria type is B

The attachment is the example answer that i want to do

[apple]

Hi Jimmy,

Do ask me if you still not understand. Thanks in advance

[mdmackillop]

Hi Apple,
Please repost your example showing the desired result.

[apple]

Hi,

The attachment is the answer that i want. Before that, the sheet is without highlight. After press highlight button, this is the answer that i desire. Thanks

[apple]

Hi,

To avoid confuse, i have modify the attachment to the row that i want.
Thanks

[mdmackillop]

From your example in Post 14, you are colouring Type A yellow and Type B green. I can see no connection to your criteria in Posts 8 and 10. Add some comments to each row in your example explaining why it is coloured as it is.

[JimmyTheHand]

I think I got it now!

So, you want to highlight in yellow all numbers for which these conditions are true

• characters #1-5 are numbers
• characters #7-9 = "ABC"
• Type is "A"

And, you want to highlight in green all numbers for which these conditions are true

• characters #1-5 are numbers
• characters #7-9 = "DEF"
• Type is "B"

If i got it right, then see the macros below. They do basically the same thing, except the 1st one is more robust, and works always (I believe), while the 2nd one is easier to understand, but might fail in some cases.

The second macro replaces all digits by a "~" character, and compares the result to "~~~~~-ABC" and "~~~~~-DEF". If the original number contains "~" character (e.g. 1.1.0 12~45-ABC-647891)then the macro might give false positives.


[vba]Sub Highlight()
Dim Rng As Range, c As Range
Dim WS As Worksheet
Dim S As String, SType As Long

Set WS = ActiveSheet
Set Rng = WS.Range("A1", WS.Range("A" & WS.Rows.Count).End(xlUp))
For Each c In Rng.Cells
SType = 0
S = Left(c.Offset(, 1), 5)
If CStr(Val(S)) = S Then SType = SType Or 1
S = c.Offset(, 1)
If InStr(S, "-") = 6 Then SType = SType Or 2
If Mid(S, 7, 3) = "ABC" Then SType = SType Or 4
If Mid(S, 7, 3) = "DEF" Then SType = SType Or 8
If c.Offset(, 2) = "A" Then SType = SType Or 16
If c.Offset(, 2) = "B" Then SType = SType Or 32

If SType = 23 Then c.Resize(, 3).Interior.ColorIndex = 6
If SType = 43 Then c.Resize(, 3).Interior.ColorIndex = 35
Next

End Sub[/vba]
[vba]Sub Highlight2()
Dim Rng As Range, c As Range
Dim WS As Worksheet
Dim S As String, i As Long

Set WS = ActiveSheet
Set Rng = WS.Range("A1", WS.Range("A" & WS.Rows.Count).End(xlUp))
For Each c In Rng.Cells
S = c.Offset(, 1)
For i = 0 To 9
S = Replace(S, CStr(i), "~")
Next
If Left(S, 9) = "~~~~~-ABC" And c.Offset(, 2) = "A" Then c.Resize(, 3).Interior.ColorIndex = 6
If Left(S, 9) = "~~~~~-DEF" And c.Offset(, 2) = "B" Then c.Resize(, 3).Interior.ColorIndex = 35
Next
End Sub
[/vba]

Hope this helps.

Jimmy

P.S.
Thanks, MD, for stopping by and helping to clear up the confusion.

[apple]

Hi Jimmy,

Thanks for your reply. Please refer below as u stated last mail:

• characters #1-5 are numbers
• characters #7-9 = "ABC"
• Type is "A"

• characters #1-5 are numbers
• characters #7-9 = "DEF"
• Type is "B"

My comment: Yes the in front character i want 5 digit, middle ABC and as well at the back i also need total 5 digit. The type is correct that u mention

Jimmy,

I need your help to explain your example code 1 and 2. Can explain line by line? Thanks

[apple]

Jimmy,

By the way is it i need to modify to run the macro? It seems cant run if i press F5 to run. Another thing please do explain the example coding that u post. Thanks a lot

[apple]

Hi Jimmy,

Thanks a lot. The example coding VBA that you post firstly can works. Appreciated your help. Can u please explain for me start from:

For Each c In Rng.Cells untill the end coding that u write?

[JimmyTheHand]

Hi Apple,
You got me confused again.

My comment: Yes the in front character i want 5 digit, middle ABC and as well at the back i also need total 5 digit.

Look at row #7 in the sample workbook you uploaded (post #14). It is

• Sequence: 1.1..3
• Number: 12356-ABC
• Type: A

The number has no 5 digits after ABC. Yet, line #7 is highlighted in yellow. Why?

Similarly, numbers in rows 14 and 16 (12345-DEF and 45698-DEF) don't meet the criteria of 5 digits at the end, so why are they painted green?