VLOOKUP is my first love of Excel, it is great function to get the corresponding values from a table easily, however there is situation where we might want to get the cell color and font color along with lookup value. VLOOKUP is a function which will return only values, but not the cell formats.

Thankfully Excel allows multiple options to solve a given problem. LOVE EXCEL!

Here’s a quick tutorial to help you learn how… Download example file at the end.

Option I

Using VLOOKUP in combination with Conditional Formatting

Option I Limitations

  • Works best in case the source data colors/cell formatting is done by a logic.
  • for example activity status complete = green, in-progress = yellow, delayed = red

Also Check: How to VLOOKUP with Multiple Criteria in Multiple Column

Option II

VBA Macro / UDF

Copy below macro to Module 1

Public IntClr, IntShade, FntClr, FntShade, CellAdd
Function LookupFormat(ByRef FndValue, ByRef LookupRng As Range, ByRef ColRef As Long)
    Set FindCell = LookupRng.Find(What:=FndValue, LookIn:=xlValues)
    CellAdd = Application.Caller.Address
    With FindCell
        FindRow = .Row
        FindCol = .Column
        With .Offset(0, ColRef - 1)
            With .Interior
                IntClr = .Color
                IntShade = .TintAndShade
            End With
            With .Font
                FntClr = .Color
                FntShade = .TintAndShade
            End With
            LookupFormat = .Value
        End With
    End With
End Function

Copy below macro to WorkSheet Module

Sub Worksheet_Change(ByVal Target As Range)
    If CellAdd = "" Then Exit Sub
    RefChk = IntClr & IntShade & FntClr & FntShade
    If RefChk = "" Then Exit Sub
    With Range(CellAdd)
        With .Interior
            .Color = IntClr
            .TintAndShade = IntShade
        End With
        With .Font
            .Color = FntClr
            .TintAndShade = FntShade
        End With
    End With
    IntClr = ""
    IntShade = ""
    FntClr = ""
    FntShade = ""
    CellAdd = ""
End Sub

Download the example file here Vlookup_Format.xlsm [RE-UPLOADED 22/OCT/2022]

Hope this helps! Happy Exelling!

Please let me know your thoughts in the comments section below.

Also Read:

Comments

  1. Joe

    How would you do it across multiple worksheets? I have a workbook with 50 worksheets. I use a drop dow to go to the worksheet and retrieve data from certain columns. I have defined names in the workbook.

    1. Raghu R Article Author

      Macro works well with reference in a different/another worksheet. But it will need range as reference, I mean constructed/indirect range will not work. However, the given macro can be extended to recognize constructed/indirect ranges to suite your needs.

  2. EMY TSARKAS

    sorry, but i cannot do it! could you please help?
    60018 34,45 60018 here I want to return the cell colour and the price
    60021 65,75 60021
    60024 41,31 60024
    60029 36,70 60029
    60030 67,78 60030
    60033 80,32 60033
    60034 44,62 60034
    60035 40,54 60035
    60038 36,77 60038
    60041 42,67 60041
    60044 52,38 60044
    60048 117,80 60048
    60049 54,96 60049

    1. Raghu R Article Author

      Please download the sample excel file with the macro, it should work for you. Please do let me know if any questions.

  3. Jose Romero

    Hi Raghu, I am a bit confused about why do we need a macro in worksheet function, and how does this work on this case? Could you please explain? Many thanks in advance.

    1. Raghu R Article Author

      Hi Jose, thanks for asking.

      Worksheet function was brought in, as it became difficult or inconsistent to apply cell formatting to cell from where the UDF was called. there might be another way without involving worksheet functions but this was the quick fix I could come up with.

  4. Sam

    Hi, this code works but cannot handle multiple cells at once. So for example, once I try to drag the filter handle to apply this through multiple cells, the cells only become the first cell color. Essential the original formatting is lost for the first cell color.

    1. Raghu R Article Author

      yes, you are right, cell will indeed loose the original formatting and UDF will leave the trace of last vlookup formatting!

      This macro is very basic version is to illustrate how to get cell formatting by using vlookup, it can be extend to suite specific needs.

  5. Veera

    Hello Raghu,

    it is possible to get lookup value including interior color using UDF, but the source data is in another workbook and getting lookup value in another workbook.

  6. Dan Schonberg

    I’ve used the code and it outputs exactly what I need, unfortunately its not updating unless i manually click on the cell, click on the formula and then hit enter. Even a manual calculate now sheet or workbook doesnt work. Is there a way for it to refresh it easier than the way I described? I have 200,000 points of data to do this to

Leave a Reply

Your email address will not be published. Required fields are marked *

18 + 4 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.