Excel does not recognize formats of pasted Word documents

Question

I am trying to use Excel to remap some text in a Word document to generate a file formatted for an online education tool.
The text from Word is a series of multiple choice questions and answers. The correct answer letter in the Word file is formatted as Bold and Underlined. After pasting into Excel I wanted to change the correct (bold, underlined) letter to the word “Correct”.
I was hoping to move through the answer letters, testing if they were Bold, and replacing the Bold entries with the word “Correct”.

Excel VBA does not see the correct answer as Bold. (I am not allowed to paste images yet, so I will describe what I am seeing:)

Cell B17 is formatted as Bold and Underlined. The highlighted buttons on the menu bar indicate Bold and Underline as well.
If I click on the Font menu, I see that the format is essentially null. There is no selection of Regular, Bold, Italic, Bold Italic.

I cannot figure out how to get VBA to recognize the font contained in cell B17 as Bold. The other cells B13, B15, B19 and B21 show up as a Regular font. It is only cell B17 that seems to have problems, even though visually it appears to be Bold and Underlined. I tried getting the code to recognize the font as “not regular” but it does not recognize that either. It seems that the font information is null or non-existent. I am not sure how to proceed that this point.
It seems to be a bug in either the Excel copy/paste or the way Excel stores font information. It seems the menu bar has the right information, but the Font menu does not. Either way VBA is not able to access the information.
Again, my code is trying to insert the word “Correct” in the cells with a Bold font.

Sub IsBold()
START:
    For jump = 1 To 5
        'If ActiveCell.Font.Bold = True Then  (I was trying this,  when it didn’t work I tried the line below)
        If ActiveCell.Font.FontStyle <> "Regular" Then  (this didn’t work either)
            ActiveCell.Value = "Correct"
        End If

        ActiveCell.Select
        Selection.End(xlDown).Select
    Next jump

    Selection.End(xlDown).Select

    GoTo START:
End Sub

Tim Williams Tim Williams · Accepted Answer · 2014-11-19T19:50:14

Likely the cells which appear bold really have mixed font formats (there may for example be a space at the end of the text which is not formatted as Bold).

You may need to look at the Characters collection and test each one for Bold

Sub TestForBold()

    Dim i As Long

    Debug.Print "Cell is Bold?", Selection.Font.Bold = True '>>Null if mixed

    For i = 1 To Selection.Characters.Count
        Debug.Print i, Selection.Characters(i, 1).Text, _
                    Selection.Characters(i, 1).Font.Bold = True
    Next i


End Sub

Excel does not recognize formats of pasted Word documents

1 Answers