Find word and 6 digit number

Try this :-

Sub HighLightSDP()
        Dim sPos As Long, sLen As Long
        Dim rng As Range
        Dim findMe As String
        ' Dim i As Integer 'Used in previous Sub
        Dim t As Integer
        Dim SearchArray

        ' To search for word and color it 'SearchArray = Array("SDP", "WORD2")
        SearchArray = Array("SDP")

        For t = 0 To UBound(SearchArray)

            Set rng = Range("H1:H10000")
            findMe = SearchArray(t)
    
            For Each rng In rng
                With rng
                    If rng.Value Like "*" & findMe & "*" Then
                        If Not rng Is Nothing Then
                            For i = 1 To Len(rng.Value)
                                sPos = InStr(i, rng.Value, findMe)
                                sLen = Len(findMe)
    
                                If (sPos <> 0) Then
                                    rng.Characters(Start:=sPos, Length:=sLen).Font.Color = RGB(255, 0, 0)
                                    rng.Characters(Start:=sPos, Length:=sLen).Font.Bold = True
                                    i = sPos + Len(findMe) - 1
                                End If
                            Next i
                        End If
                    End If
                End With
            Next rng
        Next t
End Sub

Select allOpen in new window

I don't understand, the code you have uploaded does not have any changes. Did I do something wrong?

What if there are only 5 digits, or more than 6, or additional characters after the 6 digits? Is SDP always at the beginning of the value?

What I am looking for will only have 6 digits.

This does not answer my questions at all. Please show some examples with different cases you will see, and the expected results.

In the text, will a number preceded by SDP with six digits and it looks like this SDPXXXXXX or SDP<space> XXXXXX, and sometimes just XXXXXX. And in the text, may be several occurrences of these combinations.
As you can see by my uploaded code I have the SDP covered and I can turn it red. What I need to do is find the 6-digit (XXXXXX) number and turn it red.https://www.experts-exchange.com/questions/28983974/Find-word-and-6-digit-number.html?anchor=a41928563¬ificationFollowed=180980965#

I will type a lot of text that means nothing SDP010101, but it will show how these combos may appear done by CJR SDP 101010 on 12/18/16. And finally, there are folks that put this information in without the preceding three letters like this 123456.

Ok, what about
   1234567
   123456 SDP 654321 123
   1234567 xyz SPD

1234567 No
   123456 SDP 654321 123 No
   1234567 xyz SPD No

To be honest any 6 digit number should be highlighted and the auditor can pick out the correct ones.

Have you considered using conditional formatting, perhaps together with a quick VBA function to use LIKE, this below if just related to your "any 6 digit number should be highlighted" requirement?

e.g. create a new module in VBA (Alt F11).

Function TestCell(value As String, testpattern As String) As Boolean
    TestCell = value Like testpattern
End Function

Then in a cell this would check if there are 6 digits in a row:

=testCell(A1,"*[0-9][0-9][0-9][0-9][0-9][0-9]*")

So highlight your area and create a new conditional formatting rule, use the cell ref for the top/left corner of your area as above and choose a suitable highlight of colour or fill etc:

Example few lines of sheet attached.

Steve
Test-for-number.xlsm

I believe this will highlight all 6 digit numbers.

Sub HighLightSDP()
    Dim cel As Range
    Dim intChar As Integer
    Dim intCount As Integer
    Dim lngLastRow As Long

    lngLastRow = Range("H1048576").End(xlUp).Row
    For Each cel In Range("H1:H" & lngLastRow)
        intCount = 0
        For intChar = 1 To Len(cel)
            If IsNumeric(Mid$(cel, intChar, 1)) Then
                intCount = intCount + 1
            Else
                If intCount = 6 Then
                    Highlight6 cel, intChar - 6
                    intCount = 0
                Else
                    intCount = 0
                End If
            End If
        Next
        If intCount = 6 Then
            Highlight6 cel, intChar - 6
        End If
    Next
End Sub
Private Sub Highlight6(cel As Range, intStart As Integer)
    cel.Characters(Start:=intStart, Length:=6).Font.Color = RGB(255, 0, 0)
    cel.Characters(Start:=intStart, Length:=6).Font.Bold = True
End Sub

Select allOpen in new window

What about this slight change to above too?

Sub HighLightSDP()
     Dim cel As Range
     Dim intChar As Integer
     Dim intCount As Integer

     For Each cel In Range("H1:A10000")
         For intChar = 1 To Len(cel) - 5
             If Mid(cel, intChar, 6) Like "[0-9][0-9][0-9][0-9][0-9][0-9]" Then Highlight6 cel, intChar
         Next
    Next
End Sub
Private Sub Highlight6(cel As Range, intStart As Integer)
    cel.Characters(Start:=intStart, Length:=6).Font.Color = RGB(255, 0, 0)
    cel.Characters(Start:=intStart, Length:=6).Font.Bold = True

End Sub

Select allOpen in new window

Steve, that could be an improvement, but it incorrectly highlights 12345678 or any string of numbers longer than 6.

Also see my lines 5 to 8 which I changed while you were developing your solution.

yes good plan.  I suppose ideally we would need an example sheet with test data in.. I was trying to decide what could count as a delimiter to 6 digit number as it could be I suppose any non numeric character or the end of the string.

Also how to handle something like:    123456123456 - should that match both etc?

I'm not around for a while now anyway so will shut up!

Steve

I still think we would ideally need an actual example sheet with a couple of dozen example lines at least and a "yes"/"no" against ones that shouldn't be formatted.

We have all come up with different but related ways of doing the same thing.... depends a little whether there is ever likely to be 7 or more digit numbers, or if a mis-typed 7 digit number should be included or only exactly 6 etc. etc.

If this is just for an auditor to look down it probably doesn't matter either way if SDP is highlighted say as long as the 6 digits are highlighted to make it get looked at.

Steve

I just posted as a last trial to regain some OP attention ... ;-)

Fair enough.... appears to have been member for 10 years, but not used for last 5 years and had complimentary membership saying "employee" issued for a while last year unless I read profile wrong?  Does seem we are all talking to ourselves though...

No gentlemen I am here.
Answer to Steve Knight's question: there will only be a 6 digit number. If it should go to 7 digits it will be long after I am out of here.
I have not tested this yet so I won't be closing this right now, hope to test later tonight.

Thank you all for your submission.

Sorry I was so l was so late getting back to you, was on vacation.
Thank you gentlemen for all the help.