How to maintain the VLOOKUP font format

< Back to Search results

 Category: [Excel]  Demo Available 

How to maintain the VLOOKUP font format




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?




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!


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then
    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.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.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to insert VLOOKUP into cell with variable array
How to use VLOOKUP (example)
How to use the VLOOKUP formula
How to use IF and VLOOKUP formulas together


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)