How to VLOOKUP to get Cell Color and Font Color along with Lookup Value

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

Hope this helps! Happy Exelling!

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

Also Read:

Show Comments


  1. Joe
    • Raghu R
    • Raghu R
  4. Jose Romero
    • Raghu R
  5. Irma
  6. Sam
    • Raghu R

Leave a Reply

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