引用一个单元格，但保留源单元格中文本的格式 [英] reference a cell but keep formatting of text from source cell

问题描述

我有一个带有一些文本的单元格. 说出单元格A1.

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

当我引用此单元格时，在单元格B1中说:

When I reference this cell, say in cell B1:

=A1

在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.

推荐答案

根据我的评论:

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

上面的代码意味着只要对单元格A1( > A1)进行了任何更改-这样，每次更改时，代码就不会每次执行是在您的工作表上制作的-这样做会减慢一切速度)，A1中的所有内容都将复制到B2

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

要使用，只需

• 右键单击工作表的名称(底部的标签)，然后选择查看代码"
• 将代码粘贴到
• 根据需要将任何单元格引用更改为所需的单元格引用(即，将[A1]替换为[A3]或[A1:A4]或[blah](即，命名的单元格/范围))
• 关闭窗口.要保存工作簿，如果要保留宏，则必须另存为.xlsm或.xlb.

• 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]是ThisWorkbook.ActiveSheet.Range ("A1")的简写.通常，我建议不要将其用作ActiveSheet，这意味着如果代码在ThisWorkbook中的任何工作表上运行，它将复制并粘贴在的A1和B1上该表格，而您可能只需要特定表格上的特定单元格.
  • 但是，在这种情况下，该代码仅适用于1张纸，因此没有问题.
  • [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.

    这篇关于引用一个单元格，但保留源单元格中文本的格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！