Bolding a specific part of cell

Question

I have a cell that is referenced as ="Dealer: " & CustomerName. CustomerName is a dictionary referenced name. How could I go along of bolding only "Dealer:" and not the Customer name.

Example:

Dealer: Josh

I have tried

Cells(5, 1).Characters(1, 7).Font.Bold = True

But it only seems to work on non referenced cells only. How could I get this to work on a referenced cell?

Answer 1

You can use the below function to bold some input text within a formula

So in your cell you can now type =Bold("Dealer: ")&CustomerName

To be precise – this will only embolden alphabetical characters (a to z and A to Z) all others will be left unchanged. I haven’t tested it on different platforms, but seems to work on mine. May not be supported for all fonts.

 Function Bold(sIn As String)
    Dim sOut As String, Char As String
    Dim Code As Long, i As Long
    Dim Bytes(0 To 3) As Byte

    Bytes(0) = 53
    Bytes(1) = 216

    For i = 1 To Len(sIn)
        Char = Mid(sIn, i, 1)
        Code = Asc(Char)
        If (Code > 64 And Code < 91) Or (Code > 96 And Code < 123) Then
            Code = Code + IIf(Code > 96, 56717, 56723)
            Bytes(2) = Code Mod 256
            Bytes(3) = Code \ 256
            Char = Bytes
        End If
        sOut = sOut & Char
    Next i
    Bold = sOut
End Function

Edit:

Have made an effort to refactor the above to show how it works, rather than have it peppered with magical numbers.

  Function Bold(ByRef sIn As String) As String
     ' Maps an input string to the Mathematical Bold Sans Serif characters of Unicode
     ' Only works for Alphanumeric charactes, will return all other characters unchanged

     Const ASCII_UPPER_A As Byte = &H41
     Const ASCII_UPPER_Z As Byte = &H5A
     Const ASCII_LOWER_A As Byte = &H61
     Const ASCII_LOWER_Z As Byte = &H7A
     Const ASCII_DIGIT_0 As Byte = &H30
     Const ASCII_DIGIT_9 As Byte = &H39
     Const UNICODE_SANS_BOLD_UPPER_A As Long = &H1D5D4
     Const UNICODE_SANS_BOLD_LOWER_A As Long = &H1D5EE
     Const UNICODE_SANS_BOLD_DIGIT_0 As Long = &H1D7EC

     Dim sOut As String
     Dim Char As String
     Dim Code As Long
     Dim i As Long

     For i = 1 To Len(sIn)
        Char = Mid(sIn, i, 1)
        Code = AscW(Char)
        Select Case Code
           Case ASCII_UPPER_A To ASCII_UPPER_Z
              ' Upper Case Letter
              sOut = sOut & ChrWW(UNICODE_SANS_BOLD_UPPER_A + Code - ASCII_UPPER_A)
           Case ASCII_LOWER_A To ASCII_LOWER_Z
              ' Lower Case Letter
              sOut = sOut & ChrWW(UNICODE_SANS_BOLD_LOWER_A + Code - ASCII_LOWER_A)
           Case ASCII_DIGIT_0 To ASCII_DIGIT_9
              ' Digit
              sOut = sOut & ChrWW(UNICODE_SANS_BOLD_DIGIT_0 + Code - ASCII_DIGIT_0)
           Case Else:
              ' Not available as bold, return input character
              sOut = sOut & Char
        End Select
     Next i
     Bold = sOut
  End Function

  Function ChrWW(ByRef Unicode As Long) As String
     ' Converts from a Unicode to a character,
     ' Includes the Supplementary Tables which are not normally reachable using the VBA ChrW function

     Const LOWEST_UNICODE As Long = &H0              '<--- Lowest value available in unicode
     Const HIGHEST_UNICODE As Long = &H10FFFF        '<--- Highest vale available in unicode
     Const SUPPLEMENTARY_UNICODE As Long = &H10000   '<--- Beginning of Supplementary Tables in Unicode. Also used in conversion to UTF16 Code Units
     Const TEN_BITS As Long = &H400                  '<--- Ten Binary Digits - equivalent to 2^10. Used in converstion to UTF16 Code Units
     Const HIGH_SURROGATE_CONST As Long = &HD800     '<--- Constant used in conversion from unicode to UTF16 Code Units
     Const LOW_SURROGATE_CONST As Long = &HDC00      '<--- Constant used in conversion from unicode to UTF16 Code Units

     Dim highSurrogate As Long, lowSurrogate As Long

     Select Case Unicode
        Case Is < LOWEST_UNICODE, Is > HIGHEST_UNICODE
           ' Input Code is not in unicode range, return null string
           ChrWW = vbNullString
        Case Is < SUPPLEMENTARY_UNICODE
           ' Input Code is within range of native VBA function ChrW, so use that instead
           ChrWW = ChrW(Unicode)
        Case Else
           ' Code is on Supplementary Planes, convert to two UTF-16 code units and convert to text using ChrW
           highSurrogate = HIGH_SURROGATE_CONST + ((Unicode - SUPPLEMENTARY_UNICODE) \ TEN_BITS)
           lowSurrogate = LOW_SURROGATE_CONST + ((Unicode - SUPPLEMENTARY_UNICODE) Mod TEN_BITS)
           ChrWW = ChrW(highSurrogate) & ChrW(lowSurrogate)
     End Select

  End Function

For reference on the unicode characters used see here http://www.fileformat.info/info/unicode/block/mathematical_alphanumeric_symbols/list.htm

The wikipedia page on UTF16 shows the algorithm for converting from Unicode to two UTF16 code points

https://en.wikipedia.org/wiki/UTF-16

Answer 2

As they already told you can't format a partial cell value if this latter derives from a formula/function in that same cell

However there may be some workarounds that may suit your needs

Unluckily I can't actually grasp your real environment so here are some blind shots:

---

1st "environment"

You have a VBA code running that at some point writes in a cell like:

Cells(5, 1).Formula = "=""Dealer: "" & CustomerName"

and you want to have the "Dealer:" part bold

• the most straightforward way would then be

  With Cells(5, 1)
      .Formula = "=""Dealer: "" & CustomerName"
      .Value = .Value
      .Characters(1, 7).Font.Bold = True
  End With
  

• but you could also use the Worksheet_Change() event handler as follows:

  your VBA code is only

  Cells(5, 1).Formula = "=""Dealer: "" & CustomerName"
  

  while placing the following code in the relevant worksheet code pane:

  Private Sub Worksheet_Change(ByVal Target As Range)
      With Target
          If Left(.Text, 7) = "Dealer:" Then
              Application.EnableEvents = False '<-- prevent this macro to be fired again and again by the statement following in two rows
              On Error GoTo ExitSub
              .Value = .Value
              .Characters(1, 7).Font.Bold = True
          End If
      End With
  
  ExitSub:
      Application.EnableEvents = True '<-- get standard event handling back
  End Sub
  

  where On Error GoTo ExitSub and ExitSub: Application.EnableEvents = True shouldn't be necessary, but I left them as a good practice when Application.EnableEvents = False id used

---

2nd "environment"

You have cell(s) in your excel worksheet containing a formula, like:

="Dealer:" & CustomerName

where CustomerName is a named range

and your VBA code is going to modify the content of that named range

in this case the Worksheet_Change() sub would be triggered by the named range value change and not by the cell containing the formula

so I'd go checking if the changed cell is a valid one (i.e. corresponding to a well known named range) and then go with a sub that scans a predefined range and finds and format all cells with formulas that use that `named range, like follows (comments should help you):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If Not Intersect(ActiveWorkbook.Names("CustomerName").RefersToRange, Target) Is Nothing Then
            Application.EnableEvents = False '<-- prevent this macro to be fired again and again by the statement following in two rows
            On Error GoTo ExitSub
            FormatCells Columns(1), "CustomerName" '<-- call a specific sub that will properly format all cells of passed range that contains reference to passed "named range" name
        End If
    End With

ExitSub:
    Application.EnableEvents = True '<-- get standard event handling back
End Sub

Sub FormatCells(rng As Range, strngInFormula As String)
    Dim f As Range
    Dim firstAddress As String

    With rng.SpecialCells(xlCellTypeFormulas) '<--| reference passed range cells containg formulas only
        Set f = .Find(what:=strngInFormula, LookIn:=xlFormulas, lookat:=xlPart) '<--| search for the first cell in the referenced range containing the passed formula part
        If Not f Is Nothing Then '<--| if found
            firstAddress = f.Address '<--| store first found cell address
            Do '<--| start looping through all possible matching criteria cells
                f.Value = f.Value '<--| change current cell content into text resulting from its formula
                f.Characters(1, 7).Font.Bold = True '<--| make its first 7 characters bold
                Set f = .FindNext(f) '<--| search for next matching cell
            Loop While f.Address <> firstAddress '<--| exit loop before 'Find()' method wraps back to the first cell found
        End If
    End With
End Sub