How to copy color of the conditional formatting cell to another excel file

Question

On one sheet I have a data which has conditional formatting color.

I want to copy and paste that into another file, I was able to paste value, column width etc. but I couldn't paste the color from the conditional formatting.

I researched and the suggestion was to paste it in word and then back to excel but that ruins my excel row and column formatting.

How to do that? Is it possible?

Solution 1

I would like to suggest you two possible methods. One is Non-programming & Second is Programming(VBA Macro).

Non-Programming Method:

1. Open both Workbooks.
2. Copy a cell from the original Workbook's Sheet, (from where you want to Copy the Conditional Formatting) to an unused position in the destination Workbook's sheet.
3. Open the Manage Rules option of Conditional Formatting.
4. Select Show formatting rules for This Worksheet.
5. For each Rule, adjust the Applies to match the range you require.
6. Click the Range button to the right of the Applies to.
7. Click-drag-select from the top left cell to the bottom right cell.
8. Click the Range button to return to the Conditional Rules Manager.
9. Click OK or Apply to get the result.

Programming Method:

1. At Source File press Alt+F11 to open the VB Editor.

2. Copy & paste this code as Standard module.

   Sub CopyFormat()
   Application.DisplayAlerts = False
   Dim wbSource As Workbook
   Set wbSource = Workbooks.Open(Filename:="source.xlsm", UpdateLinks:=3)
   wbSource.Sheets(1).Range("A1:H100").Copy
   Selection.PasteSpecial _ 
   Paste:=xlPasteValues
   Selection.PasteSpecial _ 
   xlPasteFormats
   
   wbSource.Close
   Application.DisplayAlerts = True
   End Sub
   

Note:

1. You can edit Worbook & Sheet name as per your need.
2. Adjust cell references for the Copied Range as needed.

Solution 2

If you do not need the formula for the conditional formatting, if you copy it to MS Word and back to Excel the color should be copied as well.

Comments

[Lee]

Open 2 Workbook in the same instance, then you can copy format color to another workbook.