Search code examples
excelvbaformattingcopycell

Copying the cell value preserving the formatting from one cell to another in excel using VBA

In excel, I am trying to copy text from one cell to another cell in another sheet. The source cell contains formatted text (bold,underlined,different colors). But when I copy the text using VBA to the other cell, the formatting is lost.

I know it is because excel is copying only the text value. Is there a way we can read the HTML text (rather than plain text) from a cell?

I have googled this and did not get any answers. I know that if we use copy and paste methods, we can copy the formatting. E.g.

Range("F10").Select
Selection.Copy
Range("I10").Select
ActiveSheet.Paste

But I want to do it without a copy and paste since my destination is a merged cell and not identically sized as my source cell. Is there an option available in excel VBA to do this?

EDIT: I was able to solve it with the following code.

Range("I11").Value = Range("I10").Value
For i = 1 To Range("I10").Characters.Count
    Range("I11").Characters(i, 1).Font.Bold = Range("I10").Characters(i, 1).Font.Bold
    Range("I11").Characters(i, 1).Font.Color = Range("I10").Characters(i, 1).Font.Color
    Range("I11").Characters(i, 1).Font.Italic = Range("I10").Characters(i, 1).Font.Italic
    Range("I11").Characters(i, 1).Font.Underline = Range("I10").Characters(i, 1).Font.Underline
    Range("I11").Characters(i, 1).Font.FontStyle = Range("I10").Characters(i, 1).Font.FontStyle
Next i
Solution

  • To copy formatting:

    Range("F10").Select
Selection.Copy
Range("I10:J10").Select ' note that we select the whole merged cell
Selection.PasteSpecial Paste:=xlPasteFormats

    copying the formatting will break the merged cells, so you can use this to put the cell back together

    Range("I10:J10").Select
Selection.Merge

    To copy a cell value, without copying anything else (and not using copy/paste), you can address the cells directly

    Range("I10").Value = Range("F10").Value

    other properties (font, color, etc ) can also be copied by addressing the range object properties directly in the same way