CODEWITHSUNDEEP
excelvba
Shira L.
Shira L.

Reputation: 81

Adding string to another cell and save the original format text color

I have a main cell with string, and i need to concatenate the value of another cell to it. The text in the main cell is colored by several colors, and when i concatenate the value of the second cell the formatting of the main cell is came back to be automatic (all the colors missing). Please your assistance how to save the text colors when concatenate another value to the string.

Here is my code: (running several times)

Sub Adding_Text()
Dim STR As String

LenC = Len(Sheets("T_list").Range("W1")) 'len of main cell
LenTR = Len(Sheets("T_list").Range("W2")) 'len of the value of second cell - which will be added to main cell
STR = Sheets("T_list").Range("W2").Value '

Sheets("T_list").Range("W1").Value = Sheets("T_list").Range("W1").Value & STR & ", "

Sheets("T_list").Range("W1").Characters(Start:=LenC + 1, Length:=LenTR).Font.Color = -16711681 'yellow
Sheets("T_list").Range("W1").Characters(Start:=LenC + 1 + LenTR, Length:=2).Font.Color = -16250872 'black
End Sub

Upvotes: 0

Views: 76

Answers (2)

Error 1004
Error 1004

Reputation: 8220

You can scrap color index with :

Sub Test()

    Dim GetFontColor As Variant

    GetFontColor = Sheet1.Range("A1").Font.ColorIndex

End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

Use a blank 'helper' cell to store the original cell character formatting.

Sub Adding_Text()
    Dim i As Long

    With Worksheets("T_list")

        .Range("W1").Copy Destination:=.Range("W3")

        .Range("W1") = .Range("W3").Value2 & .Range("W2").Value2

        For i = 1 To Len(.Range("W3").Value2)
            .Range("W1").Characters(Start:=i, Length:=1).Font.Color = _
                .Range("W3").Characters(Start:=i, Length:=1).Font.Color
        Next i

        For i = i To Len(.Range("W2").Value2) + Len(.Range("W3").Value2)
            .Range("W1").Characters(Start:=i, Length:=1).Font.Color = _
                .Range("W2").Characters(Start:=i - Len(.Range("W3").Value2), Length:=1).Font.Color
        Next i

        .Range("W3").Clear

    End With

End Sub

enter image description here

Upvotes: 2

Related Questions