CODEWITHSUNDEEP
excelvbaformattingcell
OnionSandwich
OnionSandwich

Reputation: 237

Vba check if partial bolding in cell

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!

Upvotes: 8

Views: 9133

Answers (2)

Chasester
Chasester

Reputation: 692

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

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149277

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

enter image description here

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

enter image description here

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

enter image description here enter image description here

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

enter image description here

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

enter image description here

Upvotes: 11

Related Questions