Preserve formatting VBA code with INDEX MATCH formula

[melvinkoshy]

Code, Description, Unit, Rate in MASTER sheet are in Col. A, B, C & D respectively. Some portion of Description of each item is bold and italicized.
OUTPUT sheet has Code, Description, Unit, Rate, Qty, Amount. In the Output sheet Code, Description, Unit, Rate are looked up from the MASTER sheet using INDEX-MATCH formula as follows:-

In cell B2
=INDEX(MASTER!$B$2:$B$8,MATCH(OUTPUT!A2,MASTER!$A$2:$A$8))
In cell C2
=INDEX(MASTER!$C$2:$C$8,MATCH(OUTPUT!A2,MASTER!$A$2:$A$8))
In cell D2
=INDEX(MASTER!$D$2:$D$8,MATCH(OUTPUT!A2,MASTER!$A$2:$A$8))

The formatting is not preserved in the OUTPUT sheet. I am aware that we need a VBA code to preserve the formatting. How do we embed INDEX-MATCH formula in VB script?

snapshot of MASTER sheet and OUTPUT Sheet is uploaded in the following link

http://s000.tinyupload.com/?file_id=97477698287443669722

 

[lhartono]

Try this...

[COLOR=#d3d3d3]'[/COLOR][COLOR=#ff0000]put this in OUTPUT Sheet Module[/COLOR]
[COLOR=#d3d3d3]'[/COLOR]
[COLOR=#0000cd]Private Sub Worksheet_Change(ByVal Target As Range)

Dim idx     As Long

Application.EnableEvents = False

If (Target.Column = 1) And (Target.CountLarge = 1) And (Not IsEmpty(Target.Value2)) Then
    idx = Excel.WorksheetFunction.Match(Target.Value2, Sheets("MASTER").Range("$A$1:$A$8"), 0)
    If idx > 0 Then
        Sheets("MASTER").Range("B" & idx, "D" & idx).Copy Target.Resize(1, 3).Offset(, 1)
    End If
Else
    Target.Resize(1, 3).Offset(, 1).ClearContents
End If

Application.EnableEvents = True

End Sub[/COLOR]
[COLOR=#d3d3d3]'[/COLOR]

 

[melvinkoshy]

The code works perfect. As a follow-up to this question, if I want to rearrange the fields in the OUTPUT sheet as Code, Description, Qty, Unit, Rate, what is the modification to be done in the code so that when the code is entered, it would cause the Description, Unit and Rate would appear automatically?


Note: Qty is a field in OUTPUT sheet that will be manually entered.