Need color of source text in concatenated cell

[pglufkin]

I have text in several cells that are different colored text. I want to concatenate all these different texts into one cell preserving their source colors on the concatenation. Is there a function that someone has programmed that I can use? Thank you.

[mohanvijay]

Try this function to concatenate text

[vba]
Function Sarav(x As Range, Optional Deli As String = ",", Optional DT_Format As String = "dd-mmm-yy") As String
Application.Volatile
Dim L_Rng As Range
Dim Res As String
Dim T_Str As String
For Each L_Rng In x
T_Str = L_Rng.Value
If IsDate(T_Str) = True Then
If DT_Format <> "" Then
Res = Res & Format(T_Str, DT_Format) & Deli
End If
Else
Res = Res & T_Str & Deli
End If
Next
Res = Left(Res, Len(Res) - Len(Deli))
Range("b2").Copy
Range("b2").PasteSpecial xlPasteValues
Sarav = Res
End Function
[/vba]

[pglufkin]

Tried it, it is asking for a name. Please see attachment.

[Kenneth Hobs]

Since you want the Character font colors, we will need to use the Range's Characters object.

When using a UDF, you must put it in Module, not ThisWorkbook object or a worksheet object.

[pglufkin]

Since you want the Character font colors, we will need to use the Range's Characters object.

When using a UDF, you must put it in Module, not ThisWorkbook object or a worksheet object.

thank you very much, it now produces text, but the text is not colored . . . I have attached.

[Kenneth Hobs]

As a UDF, it probably won't work. Did you want all attributes or just the font color? Did you want to delimit if multiple cells and if so, what delimiter?

Maybe an example showing various expectations might help. I am thinking that a Sub would be needed.

[pglufkin]

I just need the font color. Expected output is attached. Would really like a function as I have lots of text to combine. Thank you!

[Kenneth Hobs]

One other thing, are these font colors or conditional formatted font colors?

[pglufkin]

simply font colors

[Kenneth Hobs]

UDF's have limited outputs. In the Test Sub, I put the result in D10.

For plain text, ConcatRangeChars() can be used as a UDF.

If needed a macro could check for the UDF formula and then run ConcatAndColorParts() for each. I would remove the ConcatRangeChars() in ConcatAndColorParts() if I did that. The problem then is that if the source data changed, no updates would be done.

[VBA]Sub Test_ConcatAndColorParts()
Dim target As Range, source As Range

Set target = Range("D10")
Set source = Range("Test_Text")

ConcatAndColorParts target, source
End Sub

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

[/VBA]

[pglufkin]

Ok, thank you very much, works perfectly. I concede that what i want to do needs a sub and can't do it solely with a function.

Another question related to this project, much simpler I hope. I have a cell of text with a certain color which i have named 'text'. In another cell I am trying to call that cell (+text). How can i have '+text' be the same color as 'text'? Some sort of conditional formatting? Thanks again.

[Kenneth Hobs]

If it is a one time deal when you enter the formula, the Worksheet event SelectionChange can be used.

If that formula's font color would change if the cell in the formula's font color changes, that is another issue.

[pglufkin]

To be clearer, lets say in cell C1 i have some wording . . . . it says 'ball'. The word ball has the font color of red.

In cell A1, i want to refer to C1 so that cell A1 also says 'ball' in red. If i change the color of 'ball' in C1 to blue, i also want cell A1 to change to blue. If i change the wording in cell C1 to 'base', i also want the cell in A1 to also say 'base'.

Is that possible? Thanks again for your help.

[Kenneth Hobs]

You posted the first question and then the last question in a separate thread at: http://www.excelforum.com/excel-prog...ther-cell.html

For reasons to cross-post or not see: http://www.excelguru.ca/node/7

Changing font color does not trigger a sheet event. We can trigger based on precedent cells and dependent cells to some degree. It is better though to just use a Change event to color the text in the cells as needed based on cell value changes. Changes based on formulas require Intersect and Precedent techniques in the Change event for the sheet.

Conditional formats might be a route for part of it. I don't think that it will handle both cases though.

[pglufkin]

You posted the first question and then the last question in a separate thread at: http://www.excelforum.com/excel-prog...ther-cell.html

For reasons to cross-post or not see: http://www.excelguru.ca/node/7

Changing font color does not trigger a sheet event. We can trigger based on precedent cells and dependent cells to some degree. It is better though to just use a Change event to color the text in the cells as needed based on cell value changes. Changes based on formulas require Intersect and Precedent techniques in the Change event for the sheet.

Conditional formats might be a route for part of it. I don't think that it will handle both cases though.

Thanks for the cross post link. I have read it and understand it and will heed to it. I will just camp out here for awhile then.

How could I set up for both cases, using my simple example?

[pglufkin]

As a follow on to the below, how would you copy over to the target, character by character, the complete font attributes of the source (size, font, boldness, color, underline, italicize, etc . . .)

UDF's have limited outputs. In the Test Sub, I put the result in D10.

For plain text, ConcatRangeChars() can be used as a UDF.

If needed a macro could check for the UDF formula and then run ConcatAndColorParts() for each. I would remove the ConcatRangeChars() in ConcatAndColorParts() if I did that. The problem then is that if the source data changed, no updates would be done.

[vba]Sub Test_ConcatAndColorParts()
Dim target As Range, source As Range

Set target = Range("D10")
Set source = Range("Test_Text")

ConcatAndColorParts target, source
End Sub

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

[/vba]