OzGrid

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!

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.

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:

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.

Gallery