HLookup w/ Source Formatting Preserved

Hello,

I am working on a project which requires me to use the HLookup function to reference data from Sheet 1 and transfer it into Sheet 2. The data in Sheet 1 is color coded and the cells have a color fill on them according to certain criteria. I am looking to preserve this information after the HLookup is executed and want the information in Sheet 2 to have the same formatting. Unfortunately, I cannot use conditional formatting on this job.

Any help would be appreciated, and I don't mind Macros or VBA as long as the instructions are straight forward. If you know of another function that can achieve this functionality, I don't mind exploring that path either. I have attached a simplified version of my sheet to this thread.

Thanks!

Hello All, thanks for your help. I was able to use the code provided in the link below. The code doesn't do Vlookup, but I was able to get around that by using a match function nested in the Vlookup.

[www] .mrexcel [dot com]/forum/excel-questions/554340-can-vba-vlookup-keep-cell-formatting-3.html

Hi. I think you'll need to use some code to do this; you can't copy formatting with normal worksheet functions. Try this sample code:

Please Login or Register  to view this content.

WBD

Functions are not allowed to change the format of a cell. You'll need some logic to work out the ranges in question and use them in either of the examples above.

WBD

You're not giving much away. "Dynamic Range" doesn't tell me where the range starts and how you can determine where it ends. For example, this will stretch the lookup values to the right from E2:

Please Login or Register  to view this content.

WBD