Need VLOOKUP to Return Value Along With Background Color

Change references as and where required.
Code goes in a regular module (Module1, Module2 or whatever Module you have)

Please Login or Register  to view this content.

If you read your first post again you'll notice that all the info you give us in this last post was not in that post. What's left is to guess what you want.
How are the rows colored? If that's done with conditional formatting you can't get the colors unless you work with the formulas for the conditional formatting.
Do you want to color the cells, or whole row, in "Invoice Query" the same color as the corresponding cells in "CT Matching"?

Sorry, was trying not to make it too long with irrelevant information.

Now that you know how my data looks, my VLOOKUPs are something like the following. (Forgive me i'm no longer on my work computer so it's just basic layout)
Attachment 644333
D1 is my "Input" where i type the Invoice #
A3-E3 and like 10 rows below i have an Index Aggregate formula that pulls in all invoices from our ERP system and lists all that match that invoice #.
F3-H3 are where my VLOOKUPs are, these are the cells i want the background color to return along with the value from the VLOOKUP.
These VLOOKUPs have the following formula
In F3:
=VLOOKUP(A3,'CT Matching!$A$3:$I$5000,7,FALSE)

In G3:
=VLOOKUP(A3,'UnMatched-2019!$A$3:$I$1000,7,FALSE)

In H3:
=VLOOKUP(A3,'UnMatched-2018!$A$3:$I$500,7,FALSE)

This searches those 3 sheets in the workbook for the matching invoice. If it is found, i want it to return the column specified in the VLOOKUP and bring with it the background color. The background color is not done through conditional formatting. I do this manually.
Currently I have a PDF open on 1 screen which lists all open invoices. I type into my spreadsheet (D1) each invoice # and it brings up a list of all invoices in our system that match, and my VLOOKUPs in F/G/H match that to the open invoice to those 3 sheets that hold unreconciled transactions.

Hopefully now i've provided enough information for a solution. Thank you for the effort.

To add an attachment, go as follows:
At the bottom RH side, click on "Go Advanced"
Click on "Manage Attachments"
Click on "Choose File".
Select the file you want to attach.
Click on "Upload"
Once when uploaded, click on "Close this Window".

Hopefully that worked. My original file was 25mb so i had to re-create it but it's all there.

Bump because i'm going on hour 8 of working through my spreadsheet, having this VBA would cut my work time by atleast half. Right now if my accounts don't balance i have to manually go through a 46 page PDF and match each line to my spreadsheet to ensure it's color matched properly.

Does this do what you have in mind?
Put an invoice number in D1 in Sheet Invoice Query and run this code.

Please Login or Register  to view this content.

This does work but with a few caveats.

If the invoice # is used for multiple suppliers it does not return all the instances of that invoice number, and when my real data set is 14000+ lines there are repeats and its mandatory for my purpose that i know of it. This is why i have the index aggregate formula to return all matches and then my 3 vlookup columns look for matches of each of those inside my 3 sheets with current data.

Also in the interest of time i am looking for something that does not require a second mouse click to activate. I use this spreadsheet every month and it currently can take 1-2 days to complete. Verifying the data is matched correctly (which is what i'm trying to achieve here) can take a full day on it's own. Then longer to find the reconciled items.

(It won't let me post it in a link yet, see attachment)

These other 2 topics i've found are more what i'm looking for. I like this idea of creating an entire new function inside excel that combines vlookup and keep format (=lookupkeepformat).
This formula only needs to apply to my 3 lookup columns as i color the entire row of my data the same color i don't need a return of each cell to verify i just need it to return a single cell (preferably the cell containing invoice #) and the cell color and i know i have my match properly or that i need to investigate.

Thank you very much for your continued help and effort, it is appreciated

I can standardize the header row, that's no problem.

As for the Invoice #'s not being numbers. I export the data from our ERP and then "paste values" into my spreadsheet. it's almost 50/50 that invoices are just numbers and some are FSP20589/1511-EUDL/Jun 2019 etc.

So its a total mix of letters and numbers and having it formatted this way was the only way i could ensure my lookups were actually working, otherwise they wouldn't find the invoice even though it was physically there. So unless you have an alternate way to ensure the invoices that have letters mixed in with numbers can still be picked up by VLOOKUPs then i can't change that format.

The macro does work and returns the entire row along with the background color. It also returns multiple rows that have the same invoice number and different background color which is fantastic.

One last issue is that it's a running tally of searches. It returns the search results, when i search the next invoice it returns the results below the previous searched invoice. I would think this would bog down my spreadsheet after awhile because I could search anywhere from 500-1000 invoice numbers each time i go through this process. Not to mention they would appear at the bottom so i would have to scroll down each time to see what it returned.

I will say that there are some instances where we use the same invoice number multiple times for say phone allowances (Jun 2019/July 2019 etc) but i would say there is never more than 15 instances of the same invoice #. I would really like the search results to clear when i clear the input cell (D1). Would it be easier to give the macro a finite amount of rows to work within? Can it clear the results each time a new search is conducted?

Thank you, love the progress.

Add the one line as shown here (2nd line)

Please Login or Register  to view this content.

Apologies for the late reply, long weekend here. When i add that piece of code and enter an invoice # i get:

"Run-time error '1004'

If i'm interpreting the code correctly, that deletes everything from columns A-H? I have a data table in there that i then moved because well, i need that. I also changed it from A1:H to A4:H (i didn't know if the original code was trying to delete the input cell?).

When i did that i no longer got an error message but when i enter an invoice # into the cell the sheet freezes up. I can still click around and stuff and when i double click a cell the typing cursor shows up, when i type the letters will appear 1-6 columns left/right and 1-10 rows up/down, all sorts of weird stuff.

Attached is my full workbook that i'm currently using, minus all the sensitive data. Complete with all formulas.

I tried changing it to "ClearContents" but that did not solve it. I did not get an error but my book is frozen and i have to ctrl alt delete to be able to close excel.

Please read the following before we continue.

http://www.excelforum.com/forum-rule...rum-rules.html

http://www.excelguru.ca/content.php?184

Read and understood, apologies if i broke any rules. As I've said before, I do appreciate your time and effort.

See attached.
Code in the Workbook is:

Please Login or Register  to view this content.

Put this in the "Invoice Query" module.

Please Login or Register  to view this content.

This is fantastic, I went through my process and this was a huge time-saver. Quick visual queue if it matches or not. So thankful for this.

I would love to build on the functionality.

I would love to be able to change the background color while using your lookup macro. Is that a possibility? So if the search returns a line that is colored yellow and it should be green (or whatever the case may be) I would love to be able to change it and have it save to the original cells.

The link below is to a different forum that i found where they accomplished this. I am totally fine with this being a macro button on my sheet that i have to click.

http://www.vbaexpress.com/forum/show...-Paste-Changes

Would this be considered a separate topic? As what i originally asked for has been completed and solved to my liking should i make a new topic for what i'm asking for now?

Changing colors should not be a problem but we don't know why some are yellow, others brown or whatever.
Do you want to go through all (or a certain amount of) sheets and re-color the cells depending on a certain value in a row or what?
Or do you want to change the color in "Invoice Query" only.

Why i change colors is based off an AP Report PDF. If the invoices on my PDF are found in my spreadsheet they should be colored green on "CT Matching" and orange on "UnMatched-2019/UnMatched-2018". If you look at the spreadsheet you've been working on, there is a column in "CT Matching" labeled AP Report with a VLOOKUP in it. I export my AP Report as an excel file and put it in my workbook. That lookup searches for the invoice number in there and tells me if it's found.

This is an imperfect system as with the way the data is exported there's no way to match the supplier and the invoice together as i've done in column A of the other sheets (combined the supplier # and invoice # so i can match perfectly). So i initially color all my rows based on this, if a match is found in that column it gets colored green. If a match is found in the 2 columns to the left it gets colored yellow. There's tons of reasoning behind it and it's hard to explain without turning this into an essay.

The basics is that i color everthing by hand, i do not need or even want any automatic coloring of cells at this point. What i would like to do/have is when i do a search using your code in "Invoice Query" when i see the returned results i would like to change the color right then and there (manually because i don't want to set any rules or conditional formatting as the reasoning behind me changing colors varies and is pretty arbitrary) and when i change the color of the row of the searched results in "Invoice Query" i want it to save that change to where it originally pulled that data from.

Example using the sheet we've been working off of:
Invoice Query sheet and type 10 into C1 and use your macro
It returns 4 results
The 4th result Supplier C was found in UnMatched 2019 it's orange but it should actually be green
I highlight all the cells in that row and change it to green
If i now go and search Supplier C in sheet "UnMatched-2019" it should be green.

Right now it returns columns A-E which leaves out F-H (F-J in CT Matching) of the original data, so if you need to change the original search to return the entire thing in order for me to change the color of that entire row that is perfectly fine.

Maybe I am missing something.
If you type in 10 in Cell C1 it will have 4 results, 2 from Sheet "CT Matching" under "CT Verify" and 2 from "UnMatched-2019" under "UnMatched 2019"
Why would the fourth line in orange be changed to green and not the third line in yellow?
Just explain in a concise manner what you want changed and what determines when changes should be made or not be made.

You are wanting a lot of information on why i do things for me to make it short and sweet.

Let's change the number so we can use the color legends that i have on each spreadsheet

I have a PDF open on my other screen. It's called "Aged Payables Report"

The first invoice on that PDF is "40" from Supplier D.

Type 40 into C1 on "Invoice Query".
You get 1 result, it's Supplier D and colored yellow.

According to my color legend on "CT Matching", if it's found on the Aged Payables Report it will be colored green.
But we just looked on our Aged Payables Report and searched that invoice and it came up yellow, not green.
Well that's wrong, I must have made a mistake when i highlighted the rows in "CT Matching".
- I DO NOT use conditional formatting to color anything in my spreadsheet.

So instead of going to search that invoice in "CT Matching" and changing the color of the row that way.
I want to highlight row 4 in "Invoice Query" and change it to green.
Now if i search 40 again in C1 it will appear green and not yellow.
This is corrected, time to move on to the next invoice.

This goes the same way for the other 2 tables i have (UnMatched-2019 and UnMatched-2018)

Type 90 into C1 of Invoice Query
It found invoice 90 from Supplier I in "UnMatched 2018" and it's color yellow.
Pretend you found invoice 90 from Supplier I on the Aged Payables Report PDF on your other screen.
According to the legend in "UnMatched-2018". If there is no matching payment/invoice it should be orange (If it's found on the Aged Payables Report that means there is no payment made for that invoice yet, hence it's Aged)
That should be colored orange then. So again instead of searching "UnMatched-2018" for that row, I want to highlight row 4 in "Invoice Query" change the color to orange using the fill color button.
If i search for invoice 90 again, i fill find it in the same place, this time it will come back as orange and not yellow.

I will not be able to do anything for a few weeks. I suggest to see if someone else can help you or even start a new thread asking for help with your latest problem.
My apologies.

Ok, well thank you very much for all you've done. It has been very helpful.