Making a particular word in sentences bold but not highlighting the entire sentence before ", Lead Bookrunner"

Question

Sentence 1: Uber, $9.37MM Equity. Stifel Nicolaus, Lead Bookrunner.(I want to bold characters after "." and before ", Lead Bookrunner" and also the starting character before "," i.e. name of the bank and company name which is Stifel Nicolaus & Uber in this case. Also "." appears in 9.37MM Sentence 2: Google, $750MM Credit deal. JP Morgan, Lead Bookrunner.

My code:

Set Where = Range("P2", Range("P" & Rows.Count).End(xlUp))
  With Where
    .Value = .Value
    .Font.Bold = False
  End With
  For Each This In Where
    i = InStr(This, ", Lead Bookrunner.")
    If i = 0 Then i = Len(This) + 1
    This.Characters(1, i - 1).Font.Bold = True
  Next
For Each This In Where
    i = InStr(This, ".")
    If i = 0 Then i = Len(This) + 1
    This.Characters(1, i - 1).Font.Bold = False
  Next
For Each This In Where
    i = InStr(This, ",")
    If i = 0 Then i = Len(This) + 1
    This.Characters(1, i - 1).Font.Bold = True
  Next

The result I am getting through my code: Uber, $9.37MM Equity. Stifel Nicolaus, Lead Bookrunner. Result what I want: Uber, $9.37MM Equity. Stifel Nicolaus, Lead Bookrunner.

Answer 1

Will the format always be like TEXT COMMA TEXT FULLSTOP TEXT COMMA TEXT(Lead Bookrunner)? EX 1. TEXT (Uber) COMMA TEXT($9.37MM Equity) FULLSTOP TEXT (Stifel Nicolaus) COMMA TEXT(Lead Bookrunner) EX 2. TEXT (Google) COMMA TEXT($750MM Credit deal) FULLSTOP TEXT (JP Morgan) COMMA TEXT(Lead Bookrunner) – Siddharth Rout 25 mins ago

Yes, this format will be constant (TEXT COMMA TEXT FULLSTOP TEXT COMMA Lead Bookrunner.) – ah Pl 3 mins ago

If the format is going to remain like this then it is pretty straightforward really.

LOGIC:

1. Look for the first , from left using Instr
2. Look for the first , from right using InStrRev
3. Look for the first . from the right of the above , using InStrRev
4. Hightlight

CODE:

I have commented the code. If you still get stuck then feel free to leave a comment below.

Is this what you are trying?

Option Explicit

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim i As Long
    Dim lPos As Long, rPosDot As Long, rPosComma As Long, strLen As Long
    Dim cellValue As String
    
    '~~> Change this to the relevant sheet
    Set ws = Sheet1
    
    With ws
        '~~> Find last row
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Loop through the range
        For i = 1 To lRow
            With .Range("A" & i)
                '~~> Remove bold formatting
                .Font.Bold = False
                
                cellValue = .Value2
                
                If InStr(1, cellValue, "(") Then
                    .Font.Bold = True
                ElseIf InStr(1, cellValue, "Lead Bookrunner", vbTextCompare) Then
                    '~~> Store the length of the cell value
                    strLen = Len(cellValue)
                    
                    '~~> Find the position of "," from left
                    lPos = InStr(1, cellValue, ",")
                    
                    '~~> Find the position of "," from right
                    rPosComma = InStrRev(cellValue, ",", -1, vbTextCompare)
                    
                    '~~> We need this so that we can find the position of "." on the
                    '~~> right of the ","
                    cellValue = Left(cellValue, rPosComma)
                    
                    '~~> Find the position of "." from the right of ","
                    rPosDot = InStrRev(cellValue, ".", -1, vbTextCompare)
                    
                    '~~> Bold the characters
                    .Characters(Start:=1, Length:=lPos - 1).Font.FontStyle = "Bold"
                    .Characters(Start:=rPosDot + 1, Length:=rPosComma - rPosDot - 1).Font.FontStyle = "Bold"
                End If
            End With
        Next i
    End With
End Sub

In Action: