VBA handling In-Cell Formatting

VenturSum

Board Regular
Joined
May 23, 2010
Messages
137
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,437
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.


Code:
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

Board Regular
Joined
May 23, 2010
Messages
137
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
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,437
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

Board Regular
Joined
May 23, 2010
Messages
137
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,978
Messages
5,767,426
Members
425,412
Latest member
andrealp4444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top