Format specific part of the cell with specific font type

[YasserKhalil]

Hello everyone
I have in Range("B2:B" & LastRow) some data which looks like that

some text some data < Text 1|4 >

The same structure for all the cells in that range
I need that part < Text 1|4 > to be of Arial font type only but to keep the remainder of the cell with the old font type ...is it possible?
Note: the data is over 10,000 rows

The main thread is on this link
http://www.excelforum.com/showthread.php?t=1134479&p=4362617
But not the same request so as not to be cross poster

 

[Deepak]

Something like this...

Sub change_font()
Dim rng As Range, r As Range, start_chr As Integer, end_chr As Integer

Set rng = [e2:e34]

For Each r In rng
    start_chr = InStr(1, r.Value, "<")
    end_chr = InStrRev(r.Value, ">")
    If start_chr > 0 And end_chr > 0 Then r.Characters(start_chr, end_chr - start_chr + 1).Font.Bold = True
Next

End Sub

 

[YasserKhalil]

Thanks a lot Mr. Deepak for this perfect code
It is wonderful

Sub ChangeFontType()
    Dim Rng As Range, R As Range, start_Chr As Integer, end_Chr As Integer

    Set Rng = Range("H2:H" & Cells(Rows.Count, 8).End(xlUp).Row)

    For Each R In Rng
        start_Chr = InStr(1, R.Value, "<")
        end_Chr = InStrRev(R.Value, ">")
        If start_Chr > 0 And end_Chr > 0 Then R.Characters(start_Chr, end_Chr - start_Chr + 1).Font.Name = "Arial"
    Next R
End Sub

Now there is a problem with looping withing 10,000 rows or more .. is there a way to store the font type in an array and put it in one shot to speed up the process?
Thank you very much for this great solution

 

[Deepak]

Don't know yet that way.

At my side, Looping takes only 5s for 19000 cells where all are marked bold.

 

[YasserKhalil]

Ok Mr. Deepak I will apply iy to my original file and see the results and how long will it take?

Now I need to change my demand a little .. between these two < > I need to change the font type for only the numbers existed and as for text values remain as they are ..

 

[Deepak]

Ok Mr. Deepak I will apply iy to my original file and see the results and how long will it take?

Now I need to change my demand a little .. between these two < > I need to change the font type for only the numbers existed and as for text values remain as they are ..

here's double loop will increase the time.

Sub change_font2()
Dim rng As Range, r As Range, start_chr As Integer, end_chr As Integer

Set rng = [c2:c20000]
For Each r In rng
    start_chr = InStr(1, r.Value, "<")
    end_chr = InStrRev(r.Value, ">")
    If start_chr > 0 And end_chr > 0 Then
        For i = start_chr To end_chr
            If IsNumeric(Mid(r, i, 1)) Then r.Characters(i, 1).Font.Bold = True
        Next
    End If
Next
End Sub

 

[YasserKhalil]

Thank you very much Mr. Deepak
You are very very helpful
Best Regards