Excel - Copy Conditional Formatting, Remove Rules, Keep Format

how do i remove conditional formatting but keep formatting in excel
remove data but keep conditional formatting
copy colors but not conditional formatting
remove conditional formatting but keep format in excel
convert conditional formatting color to real color
change conditional formatting to permanent
excel lock conditional formatting
excel protect formatting from paste

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, 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  Normally, we can copy the conditional formatting rule from one range to another worksheet/workbook with the Format Painter feature in Excel. 1. Select the range whose conditional formatting you will copy, and click Home > Format Painter. 2. Shift to the destination range, and drag the paintbrush across this range.

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.

Here's a demo: http://www.bookkempt.com/2017/08/remove-conditional-formatting-but-keep.html

Remove Conditional Formatting but keep format, Remove Conditional Formatting but keep format Excel Facts. Wildcard in But after copying the cells, i can't paste and still keep the formats. To remove conditional formatting from the table only, select the score values and choose "Clear rules from selected cells" from the "Clear Rules" item in the Conditional Formatting menu. When we check Manage Rules, we see that all three rules are still intact, but now are applied to the key only. Let's undo changes one more time.

I copied my range of cells. Pasted them into Word. Recopied the range in Word and pasted back into excel.

Preserve conditional formatting on cut-paste, Delete Selection. But it's better than resetting the rules by hand, I guess. Copy your conditionally-formatted cells, paste them into a Word document, copy the cells from the Word document, paste them into the target Excel sheet. On the Ribbon’s Home tab, click Conditional Formatting, to format the values greater than a specific one select Highlight Cells Rules and then choose the option Greater Than. To drop down the list for formats click Custom Format, click the Fill tab, and click on the green fill color that you want.

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 word copying the whole table from ms excel into ms word then pasting it back to ms excel. The style is there but the conditional formatting doesn't apply anymore.

Paste formatting without conditional formatting rules, I tried Copy/ Paste Special (formats) but as soon as I deleted the cells With all the options that Excel 2010 offers for conditional formatting (traffic lights, . It will keep the same format and will delete conditional formatting. 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

Remove Conditional Formatting rule but keep the shading. : excel, Remove Conditional Formatting rule but keep the shading. to apply conditional formatting highlighting to one row of data and then copy and paste the If you're looking to keep the cell formatted regardless of the initial value in the cell, why  To copy the cell’s formatting, I select the cell, click Format Painter on the HOME tab, and select the cells I want to format. To remove conditional formatting from specific cells, select the cells, click the Quick Analysis button, and click Clear Format.

Removing Conditional Formats, but Not the Effects (Microsoft Excel), In other words, you might want to remove the conditions on which the formatting is based, but still keep the formatting that you see for those  If you copy and paste cell values that have conditional formats to a worksheet opened in another instance of Excel (another Excel.exe process running at the same time on the computer), no conditional formatting rule is created in the other instance and the format is not copied to that instance.

Remove Conditional Formatting But Keep Formats, So copying the conditionally formatted range in question and pasting via the Office Clipboard pane will drop the rules and only keep the 

Comments
  • + 1 I know you usually show what you've tried i... And I usually do not answer questions which has not shown some efforts but this is a good question :)
  • An awesomely coded and elegant solution! - No frills, no fuss!! !!!PERFECT AND THANK YOU!!!
  • Is there a way of reapplying the conditional formatting? What I want to do is to remove the conditional formatting, copy and paste the cells in a different sheet, then reapply the conditional formatting. Can this be done?
  • This little gem just saved so much time. I've been trying to find a way around leaving a conditional formatting formula applied to 15000+ cell column and this is just perfect. Thank you ! +1
  • unfortunately this does not work with borders and border line styles. Otherwise good job
  • a more complete copy routine can be found here (also copying borders e.g.): stackoverflow.com/a/56036633/1915920
  • OH WOW. How is this not the correct answer!? This is the only place where anyone has said this, and this is the easiest, non-VBA way to paste formatting.
  • Thanks, I came across this fidgeting with the office clipboard. I couldn't find anything elsewhere either.