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

R.Admin

7 years ago

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:

Share this: