VBA: Making specific text on a worksheet bold

[Magoosball]

Hello

I am trying to write a script that will look through column b (To the last row) for the specific text "Text to Format" and make this text bold. I don't want to make the entire cell bold, only the words "Text to Format".


Thank you!

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG25Apr26
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("B1", Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] n = 1 To Len(Dn.Value)
        [COLOR="Navy"]If[/COLOR] Dn.Characters(n, 14).Text = "Text to Format" [COLOR="Navy"]Then[/COLOR]
            Dn.Characters(n, 14).Font.Bold = True
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Magoosball]

Try this:-

[COLOR=Navy]Sub[/COLOR] MG25Apr26
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range("B1", Range("B" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]For[/COLOR] n = 1 To Len(Dn.Value)
        [COLOR=Navy]If[/COLOR] Dn.Characters(n, 14).Text = "Text to Format" [COLOR=Navy]Then[/COLOR]
            Dn.Characters(n, 14).Font.Bold = True
            [COLOR=Navy]Exit[/COLOR] For
        [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Hi Mick,

Thank you for the response. This isn't making anything bold on my spreadsheet and I'm not sure why. To stop the confusion, I created a table here that shows what should happen. Anything that contains the words "Text to Format" should turn bold after this script is ran.
Thank you for the help!


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]This text is part of a test. Text to Format.[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Hello Text to Format, this is just a test Text to Format.[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]This is a test in Cell (Text to Format) B3.[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Nothing is going to turn bold in this cell.[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Last (Text to Format) test in cell B5. Text to Form. Text to Format.[/TD]
[/TR]
</tbody>[/TABLE]

 

[Logit]

.

Option Explicit


Sub FindAndBold()
    Dim xFind As String
    Dim xCell As Range
    Dim xTxtRg As Range
    Dim xCount As Long
    Dim xLen As Integer
    Dim xStart As Integer
    Dim xRg As Range
    
    On Error Resume Next
    
    Set xRg = Range("B1:B30") [COLOR=#ff0000][B]'<--- Change range here[/B][/COLOR]
    On Error Resume Next
    Set xTxtRg = Application.Intersect(xRg.SpecialCells(xlCellTypeConstants, xlTextValues), xRg)
    
    If xTxtRg Is Nothing Then
        MsgBox "There are no cells with text"
        Exit Sub
    End If
    
    xFind = "Text to Format" [COLOR=#ff0000][B]'<-- Change term to bold here[/B][/COLOR]
    xLen = Len(xFind)
    
    For Each xCell In xTxtRg
        xStart = InStr(xCell.Value, xFind)
        Do While xStart > 0
            xCell.Characters(xStart, xLen).Font.Bold = True
            xCount = xCount + 1
            xStart = InStr(xStart + xLen, xCell.Value, xFind)
        Loop
    Next
End Sub

 

[Magoosball]

This worked!! Thank you!

 

[Logit]

You are welcome.