VBA "vlookup" but keeping source formatting

[vg05]

As the title mentions, I'm trying to essentially do what a vlookup does. I have 2 sheets, each sheet contains the same values in column A - but not in any specific order. Here is what I am looking to do.

On Sheet 1 - lookup value =A2, Lookup Range = Sheet 2, Return range is column C. But I want them essentially copied and pasted as values that keep the source formatting so I don't lose my background colors.

Any help would be appreciated.

Thanks.

[beyond Excel]

Hello. A question:

> Are these source formats all the same or do the cells keep changing their formats?...

[6StringJazzer]

You don't say where you want the result pasted to. For this example I am using C2.

Please Login or Register  to view this content.

But I want them essentially copied and pasted as values that keep the source formatting

This is contradictory. If you copy and paste as values you keep destination formatting. A normal copy & paste uses source formatting. Please clarify which one you want.

[vg05]

Beyond - they change. We use red, yellow, and green as sort of a "status tracker"

6 string - apologies, I was thinking too much like it was a formula that I could put in the columns that I wanted. What you gave was helpful, but I should elaborate.

The first part works well. Find matching value of A2 on WS1 in A:A on sheet2. Then, in that row copy value/format of C, and paste into Q2 on sheet1. Also the value/format of D from the row on sheet2, to R2 on sheet 1, and also the value/format of E on 2, pasted to S2 on 1.

Then repeat for each row on sheet 1 - A3 on 1 would get data from C,D,E on sheet 2 ,pasted into Q,R,S back on 1 and loop all the way through until column A was blank.

Sorry for the vagueness, still trying to learn some of this.

Thanks in advance.

[beyond Excel]

Of course what you ask for can be done.

Speaking of efficiency, this process will be between inefficient and very inefficient: Understood?...

Please upload to the Forum a workbook that contains on one sheet some of your information and on another sheet show us what you want to obtain.

[vg05]

Ive added a workbook, Please read the notes sheet first.

Thank you.

Also, the code that I've used in the past is extremely beginner level - basically just that which you can see from macros. So if there is anything complex about the vba, if you wouldn't mind giving a brief explanation regarding how it works so I can continue to learn it would be much appreciated.

Thank you!

[Marc L]

Hi,

so weird your expected result Sheet3 does not match Sheet1 & 2, so confusing …

According to your explanation just try a beginner Excel basics like explained in VBA help : Range.Find and once found, use Range.Copy …

[6StringJazzer]

I agree that your expected result sheet does not match the other data. But based on your description here is what you need. See attached. Click the button to run.

Please Login or Register  to view this content.

[vg05]

This sheet is used by multiple people. From the time I started, to the time that I created a version to attach - someone had gone in to adjust and I didn't catch it.

But this code works perfectly. Thank you very much. I added rep.