reference a cell but keep formatting of text from source cell

Question

I have a cell with some text in it. Say cell A1.

This text is formatted in a certain way - First few words are bold, line breaks, varying font size, etc

When I reference this cell, say in cell B1:

=A1

In B1 I just get a long string of text that has none of the formatting that is present on A1

Is there a way to reference and keep the formatting?

I can use format painter and it will recognise the line breaks within the cell, but aspects like the partially bold writing are still not recognised.

Answer 1

As per my comment:

Private changing As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Target.Address = [A1].Address Or changing Then Exit Sub
    changing = True
    [A1].Copy [B1]
    changing = False

End Sub

The code above means that whenever any change is made to cell A1 (specifically A1 - that way the code doesn't execute every time a change is made on your sheet - doing that would slow everything down), whatever's in A1 is copied to B2

Usage

---

To use, simply

• Right click on the name of your sheet (a tab along the bottom), and select "view code"
• Paste the code in
• Change any cell references to the ones you want (ie replace[A1] with [A3] or [A1:A4] or [blah] (i.e. a named cell/range) depending on what you need)
• Close the window. To save the workbook you'll have to save as .xlsm or .xlb if you want to keep the macro

Notes

---

• [A1] is shorthand for ThisWorkbook.ActiveSheet.Range ("A1"). Typically I would advise against using it as ActiveSheet means that if the code ran on any sheet in ThisWorkbook, it would copy and paste over the A1 and B1 of that sheet, whereas you probably only want the specific cells on a specific sheet.
  • However in this case, the code only applies to 1 sheet so that's not a problem.
• All the changing stuff is necessary because copy/paste triggers a change event; i.e. the macro triggers itself over and over until Excel stops it - not ideal! The changing variable which I declare simply acts as a signal to stop the program executing itself.