VBA Excel - Underline specific text

Question

I just want to ask if it is possible for a specific text to have an underline? As you can see in the codes below, I want the fname, stud and grd to be underlined when it shows in the Sheet2 sheet. The codes are located at Worksheet_Activate of Sheet2.

    Set ws = ActiveWorkbook.Worksheets("Sheet1")
    Lastrow = ws.Cells(Rows.Count, "C").End(xlUp).Row
        
    For r = 9 To Lastrow
        If ws.Cells(r, 3) = CStr(ThisWorkbook.Sheets("Sheet3").Range("K11").value) And ws.Cells(r, 12).value = 1 Then
                            
            fname = ws.Cells(r, 4).value
            stud = ws.Cells(r, 3).value
            grd = ws.Cells(r, 5).value + 1
            
            Text1 = "My name is "
            Text2 = " , my student ID is "
            Text3 = " and I'm grade "
        End If
            
    Next r
    
    With ThisWorkbook.Sheets("Sheet2")
        .Range("C72").value = Text1 & fname & Text2 & LRN & Text3 & grd & Text4
    End With

I tried using the .Characters(17, 13).Font.Underline = True, but I don't think I can use the 17,13 or any number since the value of fname, stud, and grd has a different length size.

question from:https://stackoverflow.com/questions/66056638/vba-excel-underline-specific-text

Answer 1

@Stax pointed you in the right direction, and it looks like you're just about there with a solution. I would further add storing the length of the fname, stud and grd strings in short variable names to minimise your code.

The relevant snippet would be (I'll let you work out how to do your loop etc.)

a = Len(fname)
b = Len(stud)
c = Len(grd)

With Sheet2.Range("C72")
    .Value = Text1 & fname & Text2 & stud & Text3 & grd
    .Characters(Start:=12, Length:=a).Font.Underline = True
    .Characters(Start:=36 + a, Length:=b).Font.Underline = True
    .Characters(Start:=51 + a + b, Length:=c).Font.Underline = True
End With