使用 VBA 复制条件格式的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11389822/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use VBA to copy conditionally formatted cells
提问by elexis
I am writing a script that copies data from one workbook to another. The latter is being used as a sort of database (not my idea). As a test I am copying ~300 rows of data, of which 3 columns conditional formatting and the rest are plain text. Copying the text is easy and near instantaneous but the formatting is more difficult. Currently I am using the below code to copy the formatted cells:
我正在编写一个脚本，将数据从一个工作簿复制到另一个工作簿。后者被用作一种数据库（不是我的想法）。作为测试，我正在复制约 300 行数据，其中 3 列条件格式，其余为纯文本。复制文本很容易并且几乎是即时的，但格式化更困难。目前我正在使用以下代码复制格式化的单元格：
thisSheet.Range("G" & CStr(rRow), "I" & CStr(rRow)).Copy
masterSheet.Range("G" & CStr(mRow), "I" & CStr(mRow)).PasteSpecial (xlPasteAll)
With ~300 rows this takes about 40 seconds, which is too slow. I cannot copy a range consisting of multiple rows as they are not pasted sequentially.
大约 300 行这需要大约 40 秒，这太慢了。我无法复制由多行组成的范围，因为它们不是按顺序粘贴的。
I experimented with the following code to try and copy formats.
我尝试使用以下代码来尝试复制格式。
masterSheet.Range("G" & CStr(mRow), "I" & CStr(mRow)).value = thisSheet.Range("G" & CStr(rRow), "I" & CStr(rRow)).value
masterSheet.Range("G" & CStr(mRow), "I" & CStr(mRow)).Font.Color = thisSheet.Range("G" & CStr(rRow), "I" & CStr(rRow)).Font.Color
masterSheet.Range("G" & CStr(mRow), "I" & CStr(mRow)).Interior.ColorIndex = thisSheet.Range("G" & CStr(rRow), "I" & CStr(rRow)).Interior.ColorIndex
'cell color and font color are the only things i am interested in
This code executes in about 3 seconds, but none of the formatting applied by the conditional formatting is copied.
此代码在大约 3 秒内执行，但不会复制条件格式所应用的任何格式。
Is there a more efficient way of copying cell and font colors that were applied by conditional formatting?
是否有更有效的方法来复制由条件格式应用的单元格和字体颜色？
采纳答案by danielpiestrak
Try adding this to the start of your code:
尝试将其添加到代码的开头：
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
And this to the end of it:
到最后：
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
If this isn't working to improve your speed to where you need I'd suggest hard coding the conditional formatting into the VBA.
如果这不能将您的速度提高到您需要的地方，我建议将条件格式硬编码到 VBA 中。
So for example if one of the conditional formatting rules made the cell Red if the number was over 100. Add that check into the VBA while it's copying the values and set the destination cell ot the desired format based on its value.
例如，如果条件格式规则之一使单元格为红色（如果数字超过 100）。在复制值时将该检查添加到 VBA 中，并根据其值设置目标单元格所需的格式。
