vba Excel - 复制条件格式，删除规则，保留格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25768023/
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
Excel - Copy Conditional Formatting, Remove Rules, Keep Format
提问by John Bustos
I know you usually show what you've tried in a question, but this is more of a "Do you have a good routine that does this?" question and I'm hoping you'll be willing to let it slide...
我知道您通常会在问题中展示您尝试过的内容，但这更像是“您是否有一个很好的例程来做到这一点？” 问题，我希望你愿意让它滑下去......
I'm working on a macro that copies cells that are conditionally formatted in a source worksheet and pastes them into an output sheet. Basically, I'm looking to keep all the formatting, shading, etc, but remove all the conditions (make the current formatting static) in the output sheet.
我正在研究一个宏，该宏复制在源工作表中有条件格式化的单元格并将它们粘贴到输出表中。基本上，我希望保留所有格式、阴影等，但删除输出表中的所有条件（使当前格式为静态）。
I've seen some solutions online - ranging from copying it first to a word document and then pasting it back, to looping through the output cells and copying format-element by format-element - and am just looking for a good, efficient way to do this.
我在网上看到了一些解决方案 - 从首先将其复制到 Word 文档然后将其粘贴回，再到遍历输出单元格并逐个复制格式元素 - 我只是在寻找一种好的、有效的方法来做这个。
Does anyone have one / a good link they'd be willing to share??
有没有人愿意分享一个/一个好的链接？
(Excel 2010)
(Excel 2010)
THANKS!!!!
谢谢！！！！
回答by Siddharth Rout
Yes it is possible :) What you need to do is change the formatting of the cells that you plan to copy by mimicking the DisplayFormatand then deleting the conditional formatting
是的，这是可能的:) 您需要做的是通过模仿DisplayFormat然后删除条件格式来更改您计划复制的单元格的格式
Sub Keep_Format()
Dim ws As Worksheet
Dim mySel As Range, aCell As Range
'~~> Change this to the relevant sheet
Set ws = ThisWorkbook.Sheets("Sheet1")
'~~> Change this to the relevant range
Set mySel = ws.Range("A1:A10")
For Each aCell In mySel
With aCell
.Font.FontStyle = .DisplayFormat.Font.FontStyle
.Interior.Color = .DisplayFormat.Interior.Color
.Font.Strikethrough = .DisplayFormat.Font.Strikethrough
End With
Next aCell
mySel.FormatConditions.Delete
'
'~~> Now Do the copying
'
'~~> Once you are done, close the sorce worksheet without saving
End Sub
回答by MarioTheHedgehog
I think I've got it with the Office Clipboard: Copy range, open the Office Clipboard pane (the tiny button in the bottom right corner of the Clipboard section under the Home tab) and paste from there.
我想我已经有了 Office 剪贴板：复制范围，打开 Office 剪贴板窗格（主页选项卡下剪贴板部分右下角的小按钮）并从那里粘贴。
Here's a demo: http://www.bookkempt.com/2017/08/remove-conditional-formatting-but-keep.html
这是一个演示：http: //www.bookkempt.com/2017/08/remove-conditional-formatting-but-keep.html
回答by andyw
I copied my range of cells. Pasted them into Word. Recopied the range in Word and pasted back into excel.
我复制了我的单元格范围。将它们粘贴到 Word 中。在 Word 中重新复制范围并粘贴回 Excel。
回答by AK_
I was trying to do the same, all answers seemed too much of work for me or based on some bug / glitch.
我试图做同样的事情，所有的答案对我来说似乎工作量太大，或者基于一些错误/故障。
What worked for me is opening ms wordcopying the whole table from ms excelinto ms wordthen pasting it back to ms excel. The style is there but the conditional formatting doesn't apply anymore.
对我ms word有用的是打开将整个表格从ms excelintoms word然后粘贴回ms excel. 样式在那里，但条件格式不再适用。
