VBA Code to combine VLOOKUP and Pastespecial

[EzoArk]

Every morning I have to manually copy and paste information from cells because VLOOKUP doesn't carry over formatting. I am very beginner to VBA.... Basically I have cells that contain information laid out like such:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product Name[/TD]
[TD]DOCKET[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]<del>1234</del>TEST[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD]2345 TEST[/TD]
[/TR]
</tbody>[/TABLE]

Any help is appreciated putting together VBA Code or functions to copy the formatting and values due to the strikethroughs through some text. I just need a way to get VLOOKUP to carry over the formatting of the strikethroughs and even print them on a new sheet.

 

[Fazza]

hello. PLEASE back-up your file before running the code, and edit as required. regards

Sub HowIsThis()


    Dim i As Long
    Dim rngLoop As Excel.Range
    Dim rngProductCodes As Excel.Range
    Dim rngLookupTable As Excel.Range
    
    If MsgBox(Prompt:="Is file backed up", Buttons:=vbQuestion + vbYesNo + vbDefaultButton2, Title:="Please backup file before running code") = vbYes Then
        MsgBox Prompt:="Lookup results will be place in the cell next to the product names", Title:="Please note"
        Set rngProductCodes = Application.InputBox(Prompt:="Please Select Product Names", Title:="EzoArk Special Lookup", Type:=8)
        Set rngLookupTable = Application.InputBox(Prompt:="Please Select Lookup Table", Title:="EzoArk Special Lookup", Type:=8)
        Application.ScreenUpdating = False
        On Error Resume Next
        For Each rngLoop In rngProductCodes
            i = Application.WorksheetFunction.Match(rngLoop.Value2, rngLookupTable.Columns(1))
            rngLookupTable.Cells(i, 2).Copy rngLoop.Offset(, 1)
        Next rngLoop
    End If


End Sub