How to maintain the VLOOKUP font format

Requirement:

The value returned from a Vlookup function looses formatting (e.g, font color). How can the user retain the font characteristics of the values contained in the lookup table?

Solution:

The following event macro (placed in the private module of the sheet with the VLOOKUP formula & the VLOOKUP 1st argument referernce -- hopefully both of the same sheet) seems to work.

It places the format of the target cell into B2 (the vlookup formula) whenever the contents of cell A1 (VLOOKUP's 1st argument) is changed. Hopefully this will get you started!

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then
    Range("E1").Select
    Cells.Find(What:=WorksheetFunction.VLookup(Range("A1"), Range("D1:F10"), 2, False), _
    After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Selection.Copy
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End If
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by thomach.