Excel - 复制条件格式，删除规则，保留格式 [英] Excel - Copy Conditional Formatting, Remove Rules, Keep Format

问题描述

我知道你通常会显示你在一个问题上尝试过的内容，但这更像是一个你有一个很好的例程吗？问题，我希望你愿意让它滑动...

我正在处理一个宏，它会复制在源中有条件格式化的单元格工作表并将其粘贴到输出表中。
基本上，我希望保留所有的格式，阴影等，但删除输出表中的所有条件（使当前的格式化静态）。

我已经在线看过一些解决方案 - 从首先将其复制到单词文档，然后将其粘贴回来，循环输出单元格，并通过格式元素复制format-element - 而且只是寻找一个很好的，高效的有没有办法这样做？

有没有人有一个/一个很好的链接，他们愿意分享？

（Excel 2010）

THANKS !!!!

解决方案

是的可能:)你需要做的是通过模仿 DisplayFormat 然后删除条件格式化

来更改您打算复制的单元格的格式>

  Sub Keep_Format（）
 Dim ws As Worksheet 
 Dim mySel As Range，aCell As Range 
 
'~~>将其更改为相关工作表
设置ws = ThisWorkbook.Sheets（Sheet1）
'~~>将此更改为相关范围
设置mySel = ws.Range（A1：A10）
 
对于每个aCell在mySel 
中使用aCell 
 .Font.FontStyle = .DisplayFormat.Font.FontStyle 
 .Interior.Color = .DisplayFormat.Interior.Color 
 .Font.Strikethrough = .DisplayFormat.Font.Strikethrough 
结束
下一个aCell 
 
 mySel.FormatConditions.Delete 
 
'
'~~>现在复制
'
 
'~~>一旦完成，关闭魔术工作表，而不保存
 End Sub 
  

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.

Does anyone have one / a good link they'd be willing to share??

(Excel 2010)

THANKS!!!!

解决方案

Yes it is possible :) What you need to do is change the formatting of the cells that you plan to copy by mimicking the DisplayFormat and then deleting the conditional formatting

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

这篇关于Excel - 复制条件格式，删除规则，保留格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！