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:
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.
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.
HI ARE THERE ANY WORKSHOPS WHERE WE CAN LEARN ADVANCED EXCEL
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
Please download the sample excel file with the macro, it should work for you. Please do let me know if any questions.
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.
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.
Hello there is not file to download for this sample
download is available, check this link Vlookup_Format.xlsm
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.
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.
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.
it should work
Are you bale to email me the Vlookup_Format.xlsm file? The link is now broken
Is it possible to get the sample file? it appears that the link no longer works
VLookup_Format.xlsm link broken again!
I spend 10 minutes and cannot locate this file anywhere: Vlookup_Format.xlsm
1. Not on this page. https://quadexcel.com/wp/vlookup-get-cell-color-font-color-along-lookup-value/
2. Not on https://quadexcel.com/wp/
download is available, check this link Vlookup_Format.xlsm
download is available, check this link Vlookup_Format.xlsm
link is broken — can you point me to another location
download is available, check this link Vlookup_Format.xlsm
Link is broken
download is available, check this link Vlookup_Format.xlsm
Uploaded file again, check this link Vlookup_Format.xlsm
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