VBA handling In-Cell Formatting

[VenturSum]

Hi All,

I'm using VBA to copy the text of a cell into a string variable.

The Source cell has in-cell formatting -- some words in the cell have bold formatting and color change; but not the whole cell.

How can I copy the data a string variable and still note the location and type of formatting??

I don't want to do a cell to cell copy.
I actually want to find and note the formatting inside the cell using VBA. So if the 1st word is bold, I can track that.

Thank you,
John

 

[AlphaFrog]

Maybe try something like this as an example.

Put this in cell A1
This is a Test of formatting.

The macro below will return this in B1
This is a Test of formatting.

Sub Format_Test()

    Dim rng As Range, vText(), i As Integer, strTemp As String

    Set rng = Range("A1")
    
    ReDim vText(1 To Len(rng), 1 To 7)
    
    For i = 1 To Len(rng)
        With rng.Characters(i, 1)
            vText(i, 1) = .Text
            With .Font
                vText(i, 2) = .Name
                vText(i, 3) = .Size
                vText(i, 4) = .Color
                vText(i, 5) = .Bold
                vText(i, 6) = .Italic
                vText(i, 7) = .Underline
            End With
        End With
    Next i
    
    'This is a Test of formatting.
        For i = 1 To UBound(vText)
            With rng.Offset(, 1).Characters(i, 1)
                .Text = vText(i, 1)
                With .Font
                    .Name = vText(i, 2)
                    .Size = vText(i, 3) + 1     'Increase font size by 1
                    .Color = vText(i, 4)
                    .Bold = Not (vText(i, 5))   'Invert Bold
                    .Italic = Not (vText(i, 6)) 'Invert Italic
                    .Underline = vText(i, 7)
                End With
            End With
        Next i
        
End Sub

 

[VenturSum]

AlfaFrog,

I can get it from your code.
You have pointed me in the right direction.

One question...
Do you know of any test to check for in-cell formatting??
So I can evaluate only such cells and not all cells.
I don't think .specialcells() checks -- I could be wrong though.

'----------------------------------------------
I'm trying to get the below results.

In cell A1
This is a Test of formatting.

Using Debug.Print; the Macro returns:

This is a [Red]Test[/Red] of [Bold][Italics]formatting[/Bold][/Italic].

Some RGB value may replace the [Red] text.
Any thoughts?

Thank You for your Help.
I truly appreciate it.

John
​

 

[AlphaFrog]

Each "i" in vText is one character and its font attributes

vText(i,1) is the character
vText(i,2) is the font name
vText(i,3) is the font size
vText(i,4) is the font color
vText(i,5) is the font bold (True or False)
vText(i,6) is the font Italic (True or False)
vText(i,7) is the font Underline

I'm not sure what you are asking in your second question.

 

[VenturSum]

AlfaFrog,

Got it. Thank you.

I think the code could first determine if there iss any in-cell formatting.
If there is, the cell could be evaluated per above.
Otherwise, I could just copy it into a string variable.

So I was wondering if there was some fancy test to see if the cell had these in-cell formats -- E.g. SpecialsCells()

I could probably use a Find() or search() function.
Just have to think about it more.

John