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

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?

1

3 Answers

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.

0

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.

0

I found a dead easy solution to this problem.

1. Copy the cells with the results of conditional formatting that you want to duplicate in another place.

2. Paste into an OpenOffice Calc spreadsheet (I used build 4.1.5) as Formatted - RTF. The formatting is still there, but OpenOffice apparently discards the conditions that created the formatting in the first place.

3. Now simply copy and paste into Excel.

4. Crack open the champagne.

This works with Excel files thousands of lines long.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy