» Search value in external workbook and paste with format style using VBA in Microsoft Excel

Question:
When entering a value in column B, value should be searched in a different workbook in row 1 the value in column A should be searched in column A of the second


Answer:
Insert the following code in the appropriate modules.

'Place the code below into the This Workbook module.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Column = 2 Then Call ReadFormatting(Target.Offset(0, -1))

End Sub



 'Place the code below into the standard module

Sub ReadFormatting(rng As Range)

   Dim varRow As Variant, varCol As Variant

   Application.ScreenUpdating = False

   Workbooks.Open "c:temptest.xls", False

   varRow = Application.Match(rng.Value, Columns(1), 0)

   varCol = Application.Match(rng.Offset(0, 1).Value, Rows(1), 0)

   If Not IsError(varRow) And Not IsError(varCol) Then

      Cells(varRow, varCol).Copy

      rng.Offset(0, 2).PasteSpecial xlPasteFormats

'    The value will carry from:

''    rng.Offset(0, 2).PasteSpecial xlPasteValues

      Application.CutCopyMode = False

   End If

   ActiveWorkbook.Close savechanges:=False

   Application.ScreenUpdating = True

End Sub