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

Leave a Reply

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