CODEWITHSUNDEEP
excelvba
Swannekin
Swannekin

Reputation: 53

Formatting individual characters in a cell - inexplicable behavior

I have code in a macro that formats individual characters in a cell. Recently, it has stopped working, and only formats all the characters or none at all.

I have reduced the code to just a few lines to test, and it fails on two different machines (one Mac, one PC). I've restarted many times. Both machines exhibit the same incorrect behavior.

Sub formattest()
    Workbooks("Text.xlsm").Worksheets("Sheet1").Cells(1, 1).Characters(Start:=1, Length:=1).Font.Color = RGB(0, 0, 0)
    Workbooks("Text.xlsm").Worksheets("Sheet1").Cells(1, 1).Characters(Start:=1, Length:=1).Font.Bold = True
End Sub

Sub formattest2()
    Workbooks("Text.xlsm").Worksheets("Sheet1").Cells(1, 1).Characters(1, 1).Font.Color = RGB(0, 0, 0)
    Workbooks("Text.xlsm").Worksheets("Sheet1").Cells(1, 1).Characters(1, 1).Font.Bold = True
End Sub

Sub formattest3()
    With Workbooks("Text.xlsm").Worksheets("Sheet1").Cells(1, 1).Characters(1, 1).font
        .Color = RGB(0, 0, 0)
        .Bold = True
    End With
End Sub

In all three subroutines, I expect only the first character in cell 1,1 to be formatted, but that is not what happens. I thought it was specific to Excel getting into a strange state, which is why I tried on two different machines and restarted. What happened and why did it once work but no longer does? Is there anything I can do to achieve the behavior I want?

Upvotes: 2

Views: 80

Answers (1)

BigBen
BigBen

Reputation: 50162

Just to close this question out: You'll run into this issue if you try to format distinct digits of a number, as opposed to characters in text.

To circumvent this, either change the format of the cell to Text, or add an apostrophe ' before the number to force it to text.

If you have to retain the number as a number, and not text, I think it's impossible.

Upvotes: 1

Related Questions