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: