Ampersand from another sheet and keeping two different text colors VBA

[zgivod]

Hi Im trying to get a code that can take data from multiple cells from sheet1 and place them in the same cell on sheet2 but the tricky part is keeping the same colors of the text from sheet1. For example sheet one A1-Roses A2-Are Read should read on sheet2 A1-RosesAre Red

 

[pbornemeier]

IF all of the characters in a cell have the same formatting applied then this code should work:

Option Explicit

Sub CopyCells()
    
    Dim rngCell As Range
    
    For Each rngCell In Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeConstants, 23).Cells
        rngCell.Copy Destination:=Worksheets("Sheet2").Range(rngCell.Address)
    Next

End Sub