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:
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:
VBA Code:
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