Using Vlookup to retrieve cell data and color

tarmand

Board Regular
Joined
Jul 11, 2002
Messages
196
I'm using Vlookup to create a duplicate of spreadsheet where I only need to provide certain column information. I am inputting all project numbers in column A of both sheets and have been doing a Vlookup to pull in the information from the other needed columns. What I can't figure out is how to make it bring in the color of the cell as well. I have to highlight the cells based on a cell containing percentage formula. I want the color that I highlight the row to copy over to the duplicate sheet.

Can I do this using Vlookup?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
In a word, no.

However, there might be other ways to achieve the result you're after, but I think you'll need to describe your data and formatting conditions in a little more detail in order to determine this. Fot instance, Conditional Formatting might be the way forward.

HTH
 
Upvote 0
Weaver is right,
Not with just a Vlookup formula (or any format for that matter).

VBA code will be required.

Post a little more detail about your layout and the vlookup formulas used.
 
Upvote 0
Sorry, I have unexpectedly been out for a few days.

My original document lists the following:

On the "ACTIVE" sheet:

Columnd "A" is project numbers.

Columns "B" through "I" are hard keyed descriptives about the project, like location, title, project manager, p.o. number, etc.

Column "J" is the hard keyed dollar amount of the purchase order.

Column "K" is a calculated field. It calculates the total expenditures that are hard keyed by me in columns "X" though "BZ". I have dates listed at the top of those columns and I key in the weekly expenditures in each project's row.

Columns "L" through "N" are calculated values that have no bearing on the formats.

Column "O" lists the percentage of the p.o. that has been spent. This is how I decide what color to make that row. I change the color manually each week according to the following criteria: Red = 100 % or more. Orange = between 95 & 99%. Blue = between 90 & 94% and yellow means there is no purchase order (column I - p.o. number and J - p.o. $ would be empty). I also use Purple to signify that a project is suspended and Green to mean we are on hold. This must be done manually as there are no calculations that tell me this.

Column "P" through "W" are more hard keyed project descriptives and comments.

On the "SEND TO ROY" sheet: (this is the sheet that is doing the vlookup)

Column "A" is a hard keyed list of all projects that Roy is interested in reviewing.

All of the rest of the columns contain vlookup functions using the number in "A" to find the corresponding information from the "ACTIVE" sheet. Not all columns from "ACTIVE" are pulled to this sheet. Only information that I need to share with Roy.

Column "J" does pull the percentage information from "ACTIVES" column "O" that I use for formatting.

Does this help?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top