CODEWITHSUNDEEP
excelvbaexcel-formula
Aaron
Aaron

Reputation: 19

Bolding Text with VBA

Here's the cell formula I'm using:

="Charge: "&(UPPER(AG343))&"    "&" Charging Staff: "&(AH343)&"    "&" Charging Staff: "&(AI343)&"    "&" CAP_ID: "&(AJ343)

Is there a way in VBA to recognize that a value is being pulled into Cell "A" from a reference to Cell "B" and to make the text representation of the reference from Cell "B" in Cell "A" bold?

I've seen posts on this subject on the limitations of Excel functions to manipulate text. I don't understand why I can use the UPPER function on the results of a cell reference or on text in general in a cell formula, but not be allowed to bold text in a formula.

Is there a way in VBA to apply the Bold property to text in a cell?

Is there a way to use the "ActiveCell.Characters" property to the cell references to get bolded text?

Upvotes: 0

Views: 1462

Answers (1)

Dy.Lee
Dy.Lee

Reputation: 7567

This is not possible with the formula, but with vba.

Sub test()
    Dim rng As Range
    Dim s As String
    Dim s1 As String, s2 As String, s3 As String
    Dim k1 As Integer, k2 As Integer, k3 As Integer
    Set rng = ActiveCell

    s1 = Range("AH343")
    s2 = Range("AI343")
    s3 = Range("Aj343")

    s = "Charging Staff: " & s1 & "    " & " Charging Staff: " & s2 & "    " & " CAP_ID: " & s3

    k1 = InStr(s, s1)
    k2 = InStr(s, s2)
    k3 = InStr(s, s3)
    rng = s
    With rng
        .Font.Bold = False
        .Characters(k1, Len(s1)).Font.Bold = True
        .Characters(k2, Len(s2)).Font.Bold = True
        .Characters(k3, Len(s3)).Font.Bold = True
    End With
End Sub

This is the same code using the for statement.

Sub test2()
    Dim rng As Range
    Dim s As String
    Dim vS As Variant, vK()
    Dim i As Integer
    Set rng = ActiveCell

    vS = Range("Ah343").Resize(1, 3)

    s = "Charging Staff: " & vS(1, 1) & "    " & " Charging Staff: " & vS(1, 2) & "    " & " CAP_ID: " & vS(1, 3)

    For i = 1 To UBound(vS, 2)
        ReDim Preserve vK(1 To i)
        vK(i) = InStr(s, vS(1, i))
    Next i
    rng = s
    With rng
        .Font.Bold = False
        For i = 1 To 3
            .Characters(vK(i), Len(vS(1, i))).Font.Bold = True
        Next i
    End With
End Sub

Upvotes: 1

Related Questions