Keeping cell format the same throught range using vb macro

[SCACCHIA]

Hi TMS,

Just a quick question. Thank you for the below macro, but when I use the macro it doesn't paste the cell formula (IE Color). Assuming format painter maybe required? Have tried to record and add it in VB but couldn't get it to work.

Sorry - Just to Clarrify, Going from VLOOKUP "A5" to Amend Logistics Report "AN2". The VLOOKUP A5 has the correct format already, I want to copy/paste the cell format as well.

Any help would be appreciated. Thanks...


Sub Test_V2()
Dim lLR As Long
Sheets("VLOOKUP").Range("A5").Copy _
Sheets("Amend Logistics Report").Range("AN2")
Application.CutCopyMode = False
With Sheets("Amend Logistics Report")
lLR = .Range("X2").End(xlDown).Row
.Range("AN2:AN" & lLR).Formula = "=VLOOKUP(X2,VLOOKUP!D:E,2,FALSE)"
End With
End Sub

[TMS]

Please use Code Tags when you post code excerpts.

Please Login or Register  to view this content.

Regards, TMS

[SCACCHIA]

Thanks TMS, that worked... Apologies, I will use Code Tags in future...

Regards,
Steve