Vba check if partial bolding in cell

Question

I'm generating XML from a list of text within a worksheet but I cant figure out how to check if the current cell has a bold word within it. What I need to do is check each cell in column A, read the text into a string, if I hit any bold words add the tags around it.

I know you can read a cells contents character by character but not its formatting.

Any help would be greatly appreciated!

Answer 1

Here is a way that you can use to check if the cell has

1. Mixed characters which are bold. In this case it will return NULL
2. All characters are bold. In this case it will return TRUE
3. None of the characters are bold. In this case it will return FALSE

Example

Sub Sample()
    Debug.Print Range("A1").Font.Bold
    Debug.Print Range("A2").Font.Bold
    Debug.Print Range("A3").Font.Bold
End Sub

To check if a cell has any bold character you can use this function as well (Either from VBA or Worksheet)

'~~> This is an additional function which will return...
'~~> TRUE if Cell has mixed/all chars as bold
'~~> FALSE if cell doesn't have any character in bold.
'~~> This can also be used as a worksheet function.
Function FindBoldCharacters(ByVal aCell As Range) As Boolean
    FindBoldCharacters = IsNull(aCell.Font.Bold)
    If Not FindBoldCharacters Then FindBoldCharacters = aCell.Font.Bold
End Function

Screenshot

And you can use .Characters().Font.FontStyle to check if each character is bold or not. Use the above Range A1 example.

Sub Sample()
    For i = 1 To Len(Range("A1").Value)
        Debug.Print Range("A1").Characters(i, 1).Font.FontStyle
    Next i
End Sub

Screeenshot

Modified Code

Sub Sample()
    For i = 1 To Len(Range("A1").Value)
        If Range("A1").Characters(i, 1).Font.FontStyle = "Bold" Then
            Debug.Print "The " & i & " character is in bold."
        End If
    Next i
End Sub

Screenshot