Visual Basic Code that does a Vlookup but retains formatting

[dab448]

Hi Everyone,

I am trying to basically do a Vlookup that can retain the font color and formatting. But as you can't do this with a Vlookup function, I need a Visual Basic function that can do this.

Can anyone help?

Thank you!!
Danielle

 

[Danielleb]

Hi Stephen,

What should I put in for the target?

Thanks!
Danielle

 

[StephenCrump]

You need to put this code in the sheet module for the worksheet in which you want to monitor changes.

Target is the range that changes and triggers the worksheet_change event. This is determined automatically by Excel. For example, if you enter "Hello" in cell A1, the Worksheet_Change event will trigger, and Target will be Range("A1").

If you copy A1 to B2:B4, then the Worksheet_Change event will trigger again, and Target will be Range("B2:B4").

 

[Danielleb]

Hi Stephen,

You are brilliant, it worked!!

Thank you so much for all of your help.

Danielle

 

[StephenCrump]

Thanks, you're welcome.