You won't be able to do this kind of thing using a worksheet function (even a
UDF).
But you could use a macro.
This sample just does A1:A3 and puts the formatted string in B1. But depending
on where your data is and how it's laid out, it could be changed to loop through
those cells.
Remember that after the cell is concatenated and formatted, it's not a formula.
Changing A1:A3 won't change B1--you have to run the macro again.
Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim DestCell As Range
Dim myStr As String
Dim lCtr As Long
Dim iCtr As Long
With ActiveSheet
Set DestCell = .Range("b1")
Set myRng = .Range("a1:a3")
myStr = ""
For Each myCell In myRng.Cells
myStr = myStr & myCell.Text
Next myCell
With DestCell
.NumberFormat = "@"
.Value = myStr
End With
End With
lCtr = 0
For Each myCell In myRng.Cells
For iCtr = 1 To Len(myCell.Text)
lCtr = lCtr + 1
With DestCell.Characters(lCtr, 1).Font
.Name = myCell.Characters(iCtr, 1).Font.Name
.FontStyle = myCell.Characters(iCtr, 1).Font.FontStyle
.Size = myCell.Characters(iCtr, 1).Font.Size
.Strikethrough = myCell.Characters(iCtr, 1).Font.Strikethrough
.Superscript = myCell.Characters(iCtr, 1).Font.Superscript
.Subscript = myCell.Characters(iCtr, 1).Font.Subscript
.OutlineFont = myCell.Characters(iCtr, 1).Font.OutlineFont
.Shadow = myCell.Characters(iCtr, 1).Font.Shadow
.Underline = myCell.Characters(iCtr, 1).Font.Underline
.ColorIndex = myCell.Characters(iCtr, 1).Font.ColorIndex
.Bold = myCell.Characters(iCtr, 1).Font.Bold
.Italic = myCell.Characters(iCtr, 1).Font.Italic
End With
Next iCtr
Next myCell
End Sub
Robert57 wrote:
Ok. I will try to explain.
I want to join text from several cells and I'm using a function
"Sammafoga(A1;a2;a3)"
But I want the text to be formatted in the recevied cell, i.e. font
size from A1 shall be 18, a2 italic and a3 normal.
How do I do that?
tnx for your answer
Robert
--
Robert57
------------------------------------------------------------------------
Robert57's Profile: http://www.excelforum.com/member.php...o&userid=29838
View this thread: http://www.excelforum.com/showthread...hreadid=495454
--
Dave Peterson