VBA: Copy columns from sheet to another and keep the formatting

[lunatu]

Hi,

Im having a macro which is copying specific columns from sheet2 to sheet1. I have some conditional formatting in sheet1 and I would like to have that to the copied columns aswell. Currently my macro is keeping the formatting from sheet2. Any ideas how to fix this?

Code Im using:

Sub CopyCell()

Application.ScreenUpdating = False

Worksheets("Sheet1").Unprotect

Application.ScreenUpdating = False

Dim LR As Long, i As Long

With Sheets("Sheet2")

LR = .Range("H" & Rows.Count).End(xlUp).Row

For i = 1 To LR

If .Range("H" & i).Value = "SalesPerson1" And .Range("J" & i).Value = "Negotiate" Then

dlr = Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row + 1

.Range("A" & i).Resize(1, 9).Copy Destination:=Sheets("Sheet1").Range("D" & dlr)

Sheets("Sheet1").Range("C" & dlr) = Date

End If

Next i

End With

Worksheets("Sheet1").Range("C:T").Locked = False

Worksheets("Sheet1").Protect

Application.ScreenUpdating = True

End Sub

 

[airforceone]

can you give us a dummy record with the same formatting as the actual record and see if we can help you, kindly use xl2bb