Index match based on code with formatting intact

[melvinkoshy]

I have “MASTER” datasheet with Item_Code , Description and Unit. When I type Item_Code in “EST” sheet, the corresponding Description and Unit against the code in “MASTER” should appear in EST sheet. The formatting as in MASTER sheet should be preserved in EST sheet also. I used the code but to no avail. Kindly guide

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range
   Dim NotFnd As Boolean
   Dim Ws As Worksheet
   NotFnd = False
   If Target.CountLarge > 3 Then Exit Sub
   If Not Target.Column = 4 Then Exit Sub
Application.EnableEvents = False
   For Each Ws In Worksheets
   If Not Ws.Name = "EST" Then
   Set Fnd = Ws.Columns(2).Find(Target.Value, , xlValues, xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
   NotFnd = True
   Fnd.Offset(, 1).Copy Target.Offset(, 4)
   Fnd.Offset(, 2).Copy Target.Offset(, 5)
   Exit For
   End If
   End If
   Next Ws
   If Not NotFnd Then MsgBox Target.Value & "not found"
Application.EnableEvents = True
End Sub

[Mumps1]

Copy and paste this macro into the worksheet code module. Do the following: right click the ESP tab name and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter an Item Code in column C and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Dim ItemCode As Range
    Set ItemCode = Sheets("MASTER").Range("B:B").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not ItemCode Is Nothing Then
        ItemCode.Offset(0, 1).Resize(, 2).Copy Target.Offset(0, 4)
    Else
        MsgBox ("Item Code not found.")
    End If
End Sub