Copy values and colors from one workbook to another without copying conditional formatting

user_8393101 0
Viewed 1943

I use this line of code to copy the contents of one workbook to another, but it only copies the values (and avoid getting #VALUE errors), but I am hoping to copy the color of the cell as well:

Workbooks("Payroll Data.xlsm").Worksheets("Pay-MR Compiled").Range("A" & Total_rows_PayMRCompiled + 1 & ":BD" & Total_rows_PayMRCompiled + Total_rows_PayMR - 1).Value = ThisWorkbook.Worksheets("Pay-MR").Range("A2:BD" & Total_rows_PayMR).Value

My issue is that when I copy Values & Source Formatting, it also copies the conditional formatting rules and because my condition is using something like NOT(ISFORMULA(A2)) function to check whether the cell is a formula or not, and the paste is only values, all will be colored.

For instance, only 1 cell is highlighted in the actual data as shown below:

Before copy

But what happens when pasting with Values and Source Formatting is:

After copy

conditional-formatting vba excel
3 Answers

I think there is no way to copy the formatting without the conditional formatting.
However, you could go the other way around: First copy values and formatting and remove the conditional formatting afterwards.

Your code could look like

Dim destRange as Range
Set destRange = Workbooks("Payroll Data.xlsm").Worksheets("Pay-MR Compiled").Range("A" & Total_rows_PayMRCompiled + 1 & ":BD" & Total_rows_PayMRCompiled + Total_rows_PayMR - 1)

ThisWorkbook.Worksheets("Pay-MR").Range("A2:BD" & Total_rows_PayMR).Copy
destRange.PasteSpecial Paste:=xlPasteValues
destRange.PasteSpecial Paste:=xlPasteFormats
destRange.FormatConditions.Delete

If the colors appears different on the destination, you might have different color schemes for the two workbooks. Have a look to https://stackoverflow.com/a/37423390/7599798 to how to copy the color settings.

user_75997980

Standing the conditional formatting formula you made clear, you could try this

Set sourceRange = ThisWorkbook.Worksheets("Pay-MR").Range("A2:BD" & Total_rows_PayMR)        
With Workbooks("Payroll Data.xlsm").Worksheets("Pay-MR Compiled").Range(sourceRange.Address).Offset(Total_rows_PayMRCompiled - 1) ' reference "target" range
    .Value = sourceRange.Value ' paste "source" range values to referenced (i.e. "target") range
    .Interior.Color = sourceRange.FormatConditions(1).Interior.Color ' color referenced (i.e. "target") range with conditional formatting color
    .Parent.Range(sourceRange.SpecialCells(xlCellTypeFormulas).Offset(Total_rows_PayMRCompiled - 1).Address).Interior.Pattern = xlNone 'clear the color of "target" sheet range corresponding to "source" range cells with formulas 
End With

If your "source" range conditional formatting has more than one formula, then just change the 1 in FormatConditions(1). to the proper format condition item number.

edited to add the Total_rows_PayMRCompiled - 1 row offset in target range

user_93128920

You can achieve this by using .DisplayFormat method. check here

Try like this

Set trgtCell = Workbooks("Payroll Data.xlsm").Worksheets("Pay-MR").Range("A2")
Set srcCell = ThisWorkbook.Worksheets("Pay-MR").Range("A2")

trgtCell.Value = srcCell.Value
trgtCell.Font.FontStyle = srcCell.DisplayFormat.Font.FontStyle
trgtCell.Interior.Color = srcCell.DisplayFormat.Interior.Color
trgtCell.Font.Strikethrough = srcCell.DisplayFormat.Font.Strikethrough
trgtCell.Font.Color = srcCell.DisplayFormat.Font.Color
user_78891290
Related