CODEWITHSUNDEEP
excelvba
tbg22
tbg22

Reputation: 11

How to format font for individual characters in cell beyond 255

I am using Excel VBA to build a cell with formatted markup text. It works fine until the cell exceeds 255 characters. At that point Font properties are not being set correctly.

I use VBA to add characters with Range.Value = Range.Value & newchar, and that works fine. But using Range.Characters(n,1).Font.property (e.g. Name, Color, Strikethrough, Underline) to set Font properties of individual characters does not work properly once the cell exceeds 255 characters. How can I apply Font properties to individual characters when a cells has more than 255 characters?

Sorry-- that was not a very good description of the problem. I was adding a character and immediately formatting font of that character, then add and format the next character, and so on. To go beyond 255 characters, I need to use &, but that clobbers the font formatting on text already in the cell.

Upvotes: 1

Views: 1519

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

Hard to say what you are doing wrong without seeing more of your code. But here is one way to format individual characters in a cell. And I've tested it with 30,000 characters in the cell (although that takes a long time). The code below will create a cell with characters and format certain characters with multiple characteristics. The example is 750 characters.

Option Explicit
Sub par()
    Dim R As Range
    Dim S As String
    Dim I As Long, J As Long

Set R = Range("A1")

For J = 1 To 10
For I = 48 To 65 + 26 * 2 + 5
    S = S & Chr(I)
Next I
Next J

Debug.Print Len(S)
R = S

For I = 1 To Len(S)
    Select Case Mid(S, I, 1)
        Case "A", "D", "Y", "a", "t", "x", "8"
        With R.Characters(I, 1)
            .Font.Bold = True
            .Font.Color = vbRed
            .Font.Strikethrough = True
            .Font.Size = .Font.Size + 2
        End With
    End Select
Next I

End Sub

enter image description here

Upvotes: 0

Ronan Vico
Ronan Vico

Reputation: 605

how are you ?

So i tried your code , and did this to fill the Cell A1 with 255 "A"'s and 255 "B"'s

here is the code

And then used Character Function

there is the result

enter image description here

Maybe you are using the wrong way the function Characters(1,256)

Characters first parameter is Init char , and second parameter is How much characters after the first one.

Hope it help you

Upvotes: 0

Related Questions