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!

Statesmanship

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

Loveinidleness

Sid, I get your Character code, but Im unclear what FinBoldCharacters is doing or how is it called? – Xanthous

Answer 2

Slight modification using https://stackoverflow.com/users/1140579/siddharth-rout post. (I would have put it in a comment but too many characters).

Call it inside your sub via:

strTempText = isItBold(frBook.Worksheets(arraySys(x, 1)).Range("H" & y))
If strTempText <> "XXX" Then
 Debug.Print strTempText
End If

And the function.

Function isItBold(aCell As Range) As String
  Dim i As Integer
  Dim Sentence_Length As Integer
  Dim checkText As String

  Sentence_Length = Len(aCell)
  checkText = ""

  For i = 1 To Sentence_Length
    If aCell.Characters(i, 1).Font.Bold = True Then
      checkText = checkText & aCell.Characters(i, 1).text
    End If
  Next i
  If Len(checkText) < 1 Then
    isItBold = "XXX"
  Else
    isItBold = checkText
  End If

End Function

Voltmer