Need VBA Code to "VLOOKUP" but keep source cell Formatting

[flyyboy84]

Hello all,

I'm new to the forum and am needing some help. Attached is a VERY simple spreadsheet with Animals on Sheet1 and a table on Sheet2, also with the same animal names, but corresponding colors (each with different formatting). I know there is no way for the VLOOKUP to output the format of the source, so I'm looking for a VBA code to Find the matching Animal from Sheet1, on Sheet2, then output the Formatted Corresponding color back to Sheet 1, adjacent to that animal. For example,

Lookup Sheet1, A3 ("Lion") on Sheet2, A2:B5, then output Sheet2, B5 (Red) to Sheet1,B3.

I am using Excel 2003, but have the ability to use 2007 if needed. Thank you for your time.

[jkj115]

Try this. This will copy the format that corresponds to the lookup value and paste the format to Sheet 1

Please Login or Register  to view this content.

[tuongtu3]

Try this. This will copy the format that corresponds to the lookup value and paste the format to Sheet 1

Please Login or Register  to view this content.

I got an error message when try your code:
"Run-time error '1004'"
Select method of Range class failed"

Regards,
tt3

[jkj115]

On what line of the code (go to VBA and hit f8 to click through the code step by step)? What are the names of your sheets

[tuongtu3]

Hi jkj115,

The error is at below line and the names are Sheet1 and Sheet2

Cells(x, 2).Select

Regards,
tt3

[jindon]

Try

Please Login or Register  to view this content.

[tuongtu3]

Try

Please Login or Register  to view this content.

Hi jindon,
Your code is perfect and thank you for your help.

Regards,
tt3

[flyyboy84]

This works PERFECTLY. Thank you so much jindon; you don't know how much time you just saved me!!!