Problem simplifed . . . can someone help me? Need target to have same attributes as source.

pglufkin

Board Regular
Joined
Jun 19, 2005
Messages
127
Posted something like this initially and no response. Will simplify what I need below:

I am trying to have the exact same type of text (font, color, boldness, italicizing, underlining, etc) in my target as I have in my source on a character by character basis. The procedure below works for color only. Is there a way I can modify this procedure to include all cell attributes for each character in my target? I looked at the Font properties and couldn't find what I needed. Maybe I need to do it in a totally different way? Thank you for any help you can give.

For Each cell In source
With cell
target.Characters(i, Len(.Value2)).Font.Color = .Font.Color
i = i + Len(.Value2) + Len(delim)
End With
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,283
Office Version
  1. 365
Platform
  1. Windows
What ranges are involved?
 

pglufkin

Board Regular
Joined
Jun 19, 2005
Messages
127
I have a total of a thousand or so cells of text, lined up in single file down a column. Part of my procedure goes and gets 3 - 10 or so cells of text at a time to concatenate them into one cell (see longer post referring to this problem with more code on what I am doing).

Each source cell has unique font properties for the text that is in that cell. When I concatenate the cells together into one cell, i want the individual characters of text in the concatenated cell to retain all the font attributes, size, color, underline, bold, italics, etc . . . of the multiple sources, and not just color.

Thank you.

P.S. Larger code below

Code:
Sub ConcatAndColorParts(target As Range, source As Range, Optional delim As String = " ")
    Dim cell As Range, v As Variant, i As Integer
     
    target.Value2 = ConcatRangeChars(source, delim)
    v = ""
    i = 1
    For Each cell In source
        With cell
            target.Characters(i, Len(.Value2)).Font.Color = .Font.Color
            i = i + Len(.Value2) + Len(delim)
        End With
    Next cell
    With Selection
        .HorizontalAlignment = xlJustify
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows(target.Row).EntireRow.AutoFit
    Application.SendKeys "{F2}"
    Application.SendKeys "{Backspace}"
    Application.SendKeys "{Enter}"
End Sub
 
Function ConcatRangeChars(charRange As Range, Optional delim As String = " ") As String
    Dim v As String, cell As Range
     
    Application.Volatile False
     
    v = ""
    For Each cell In charRange
        v = v & cell.Text & delim
    Next cell
    v = Left(v, Len(v) - Len(delim))
     
    ConcatRangeChars = v
End Function
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,283
Office Version
  1. 365
Platform
  1. Windows
Could you give some examples of the data?

Perhaps even upload a file somewhere like Box.net and post a link to it here.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,283
Office Version
  1. 365
Platform
  1. Windows
There's something wrong with the link, when I click it I'm directed to login to my own Box.net account.
 

pglufkin

Board Regular
Joined
Jun 19, 2005
Messages
127

ADVERTISEMENT

hmm, works for me, I am checking
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,283
Office Version
  1. 365
Platform
  1. Windows
Still going to my own account.

Where exactly did you get the link from?

Did you click Share next to the filename and copy the URL that appeared below?

I've only seen 'files' in the URL when I click on one of the files I've uploaded and then it appears in the browser address bar.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

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