Format different font colors for string in one cell

[MAXARN]

Hi fellas,

I have a few cell which concatenate strings from 3 other cells (2 by VLOOKUP() and one by UDF working like CONTATENATE.IF(), now I'd like to color code where they came from. I did find a few similar questions that got an answer based on the length of the strings. I can easily setup 3 columns that LEN() the strings separately.

If there's an option to retain source formatting or formatting by length of the string or something else doesn't really matter.

If any option works, the best would be the least CPU intense, which also might be the less complex.

If it helps the "3-string-summary" cells are J3:J18 (on 9 sheets) (some of them are empty) and the helper columns would be K,L,M.

 

[GlennUK]

You can't concatenate in the cell, or a use a UDF for this ... you'd use a Worksheet event ( WorkSheet_Calculate maybe ) to process the cells where the concatenation is to take place and build the string from the contents of the required cells formatting each part accordingly. Look at Sektor's post in here:

http://www.mrexcel.com/forum/showthread.php?t=563900&highlight=format

 

[MAXARN]

You can't concatenate in the cell, or a use a UDF for this ... you'd use a Worksheet event ( WorkSheet_Calculate maybe ) to process the cells where the concatenation is to take place and build the string from the contents of the required cells formatting each part accordingly. Look at Sektor's post in here:

http://www.mrexcel.com/forum/showthread.php?t=563900&highlight=format

Thanks, but I can't really figure out how to apply what little I understand of Sektors post to suit my needs.

 

[GlennUK]

Right-click the sheet tab, and choose View Code, and paste this in to the code window ... then when the sheet recalcs the strings will update:

Private Sub Worksheet_Calculate()
    For Each c In Range("J3:J8")
        With c
            If .Offset(0, 1).Value <> "" Or _
                .Offset(0, 2).Value <> "" Or _
                .Offset(0, 3).Value <> "" Then
                    .Value = .Offset(0, 1).Value & _
                                .Offset(0, 2).Value & _
                                .Offset(0, 3).Value
                    With .Characters(1, Len(.Offset(0, 1).Value)) 'First n characters...
                        .Font.Bold = True
                        .Font.ColorIndex = 3
                    End With
                    With .Characters(Len(.Offset(0, 1).Value) + 1, _
                            Len(.Offset(0, 2).Value)) 'Next n characters...
                        .Font.ColorIndex = 5
                    End With
                    With .Characters(Len(.Offset(0, 1).Value) + _
                            Len(.Offset(0, 2).Value) + 1) 'Remained characters...
                        .Font.ColorIndex = 7
                    End With
            End If
        End With
    Next
End Sub

 

[MAXARN]

Right-click the sheet tab, and choose View Code, and paste this in to the code window ... then when the sheet recalcs the strings will update:

Private Sub Worksheet_Calculate()
    For Each c In Range("J3:J8")
        With c
            If .Offset(0, 1).Value <> "" Or _
                .Offset(0, 2).Value <> "" Or _
                .Offset(0, 3).Value <> "" Then
                    .Value = .Offset(0, 1).Value & _
                                .Offset(0, 2).Value & _
                                .Offset(0, 3).Value
                    With .Characters(1, Len(.Offset(0, 1).Value)) 'First n characters...
                        .Font.Bold = True
                        .Font.ColorIndex = 3
                    End With
                    With .Characters(Len(.Offset(0, 1).Value) + 1, _
                            Len(.Offset(0, 2).Value)) 'Next n characters...
                        .Font.ColorIndex = 5
                    End With
                    With .Characters(Len(.Offset(0, 1).Value) + _
                            Len(.Offset(0, 2).Value) + 1) 'Remained characters...
                        .Font.ColorIndex = 7
                    End With
            End If
        End With
    Next
End Sub

This didn't work that well, but it could be a faulty explanation of the cell constructions on my part, I'll try to clarify with just one row as an example:

J3: These, are, from, concat1, this is from concat2, here, from, concat3
K3: 27
L3: 22
M3:19

"These, are, from, concat1,": comes from one cell
"this is from concat2, ": comes from another one cell
"here, from, concat3": can come from multiple cells (a concat.if() UDF).

The code above seemingly concatenated from KLM and formatted the numbers. I'm not sure why, but it also made excel go calculation nuts, it seemed to work for a couple of minutes if not more.


Other possibilites are:

J4: ""
J5: "from, concat2"
J6: "concat3"
J7: "conca1, concat3"

and so on.

 

[GlennUK]

You said "I have a few cell which concatenate strings from 3 other cells " ... that's what my code works on. I took the K L M columns to be the source cells. I can see now that they are not. I say again ... you cannot do this kind of formatting on a formula result ... adjust my code to point at cells that contain the source strings.

 

[MAXARN]

You said "I have a few cell which concatenate strings from 3 other cells " ... that's what my code works on. I took the K L M columns to be the source cells. I can see now that they are not. I say again ... you cannot do this kind of formatting on a formula result ... adjust my code to point at cells that contain the source strings.

Ok, can I just replace
If .Offset(0, 1).Value <> "" Or _
.Offset(0, 2).Value <> "" Or _
.Offset(0, 3).Value <> "" Then

With


If ("A1").Value <> "" Or _
("A2").Value <> "" Or _
("A3").Value <> "" Then

 

[GlennUK]

Yeah, that's right ... replace them all through the code ( I think! ). Let me know if you have any problems, and post your code back, and I'll have a look.

 

[MAXARN]

Ops, I missed an obvious alternative for me. I'll just add two columns and have three cells displaying the strings.

Thanks a bunch for the effort though. At least; now I know.

 

[GlennUK]

Whatever works is good as far as I'm concerned ... glad you got something as a solution. My pleasure to help