VLOOKUP to return value AND cell color
 

Hi. Is there a way to use VLOOKUP to lookup and return both the values
from one list to another as well as the cell fill color of the source
list?

My users have a main workbook that imports updated data to a temporary
sheet. Below shows the general layout of the temporary sheet, with
DOC being the key. I've already got a macro that copies the DOC, RAT,
and Notes from the main workbook and imports the new DOC numbers from
the external workbook. And the macro adds a VLOOKUP formula to the
second RAT and NOTES columns. The macro then copies/pastes the
updated DOC, RAT, and Notes columns back to the main workbook. And it
works well.

BUT, users add personal color coding to their DOC, RAT, and Notes
cells, and it's important to be able to carry over the color coding
with the value that's looked up.

Example sheet layout:

DOC RAT Notes DOC RAT Notes

Is that possible? Thanks!

Frank

VLOOKUP to return value AND cell color
 

Hi. Is there a way to use VLOOKUP to lookup and return both the
values from one list to another as well as the cell fill color of the
source list?

No!

My users have a main workbook that imports updated data to a
temporary sheet. Below shows the general layout of the temporary
sheet, with DOC being the key. I've already got a macro that copies
the DOC, RAT, and Notes from the main workbook and imports the new
DOC numbers from the external workbook. And the macro adds a VLOOKUP
formula to the second RAT and NOTES columns. The macro then
copies/pastes the updated DOC, RAT, and Notes columns back to the
main workbook. And it works well.

Why does the formatting not copy/paste with the rest of the data?

BUT, users add personal color coding to their DOC, RAT, and Notes
cells, and it's important to be able to carry over the color coding
with the value that's looked up.

Example sheet layout:

DOC RAT Notes DOC RAT Notes

Is that possible? Thanks!

Frank

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

VLOOKUP to return value AND cell color
 

Hi Frank,

Am Tue, 07 Jul 2015 23:49:05 -0400 schrieb Phrank:

BUT, users add personal color coding to their DOC, RAT, and Notes
cells, and it's important to be able to carry over the color coding
with the value that's looked up.

can you find out a reason for the formatting you could use in a formula?
If so, you could use cinditional formatting.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

VLOOKUP to return value AND cell color
 

Hi Garry and Claus. That's unfortunate.

The formatting does copy/paste with the rest of the data into the
first RAT and NOTES columns on the temporary sheet. However, the
formatting then goes away when the VLOOKUP is applied to second RAT
and NOTES columns, all of which gets copied back to the main workbook,
but without the formatting.

As for Conditional Formatting, the RAT could possibly work (RAT =
Rationale), so that could be standardized with some work. But NOTES
are text that are wide and varied and dependent upon the single
DOCuent entry at the time.

Any other thoughts on how I might help users make this happen?

Thank you!

Frank

On Wed, 08 Jul 2015 00:26:17 -0400, GS wrote:

Hi. Is there a way to use VLOOKUP to lookup and return both the
values from one list to another as well as the cell fill color of the
source list?

No!

My users have a main workbook that imports updated data to a
temporary sheet. Below shows the general layout of the temporary
sheet, with DOC being the key. I've already got a macro that copies
the DOC, RAT, and Notes from the main workbook and imports the new
DOC numbers from the external workbook. And the macro adds a VLOOKUP
formula to the second RAT and NOTES columns. The macro then
copies/pastes the updated DOC, RAT, and Notes columns back to the
main workbook. And it works well.

Why does the formatting not copy/paste with the rest of the data?

BUT, users add personal color coding to their DOC, RAT, and Notes
cells, and it's important to be able to carry over the color coding
with the value that's looked up.

Example sheet layout:

DOC RAT Notes DOC RAT Notes

Is that possible? Thanks!

Frank

VLOOKUP to return value AND cell color
 

Hi Frank,

Am Wed, 08 Jul 2015 06:10:25 -0400 schrieb Phrank:

Any other thoughts on how I might help users make this happen?

can you provide us two workbooks with examples of the original data and
formatting and the expected output?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

VLOOKUP to return value AND cell color
 

Yes, but I won't be able to get that until this evening. I'll post
then.

And I've had a thought. My initial thought was to use VLOOKUP, and I
posted here in the programming forum because I figured it would
involve something more than just a formula. I'm wondering if a
CopyFindOffsetPaste loop would do the trick? Here's what I'm
thinking in pseudo code:

In the temporary worksheet (CommentLoader)
- Start loop
- Look at first value in Column A and put the value in a variable
- Offset and copy the 2 adjacent cells in columns B & C
- Search in Column D for the variable loaded from column A
- Offset one column, and PasteAll

Unless I'm mistaken, that should bring the values and the formats from
columns B and C over to the appropriate location in columns E and F.

Does that sound right?

I can post the workbooks this evening. Thanks Claus.

Frank

On Thu, 9 Jul 2015 09:30:02 +0200, Claus Busch
wrote:

Hi Frank,

Am Wed, 08 Jul 2015 06:10:25 -0400 schrieb Phrank:

Any other thoughts on how I might help users make this happen?

can you provide us two workbooks with examples of the original data and
formatting and the expected output?


Regards
Claus B.

VLOOKUP to return value AND cell color
 

Hi Frank,

Am Thu, 09 Jul 2015 06:01:13 -0400 schrieb Phrank:

Unless I'm mistaken, that should bring the values and the formats from
columns B and C over to the appropriate location in columns E and F.

Does that sound right?

I had a similiar idea. But I must see the workbooks to find out if it is
working.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

VLOOKUP to return value AND cell color
 

I keep getting sidetracked and forget to send myself the workbook from
work. I'll do that this morning when I get in.

On Thu, 9 Jul 2015 12:08:51 +0200, Claus Busch
wrote:

Hi Frank,

Am Thu, 09 Jul 2015 06:01:13 -0400 schrieb Phrank:

Unless I'm mistaken, that should bring the values and the formats from
columns B and C over to the appropriate location in columns E and F.

Does that sound right?

I had a similiar idea. But I must see the workbooks to find out if it is
working.


Regards
Claus B.