Returning cell values in a comment based on values in another workbook

**Barking_Mad** · 04-09-2014, 04:58 AM

Hi,

I have Workbook1 with cell values in B7:B500. These can be conditionally formatted. I'd like vba code to do the following:

Check the cells in B7:B500 in Workbook1
If the text in a cell is a certain colour (standard excel red)
look up the value of the cell in Workbook2 (A2:A500)
Once the value is matched then find the values on the same row but in columns F and G.
Return these values in a comment in the cell containing the original value in Workbook1 (ie in B7:B500)

Any takers?

Help much appreciated.

**ragulduy** · 04-09-2014, 06:05 AM

What version of excel?

In VBA the interior.colour property of a cell is the colour without any conditional formatting applied, so if the conditional formatting changes the colour of the cell, then this will not be picked up. I'm assuming that you are referring to conditional formatted colours in your question so in later versions you can use .displayformat but in 2003 and earlier you need to use this sort of thing:

Please Login or Register  to view this content.

**Barking_Mad** · 04-09-2014, 06:23 AM

Hi,

It's 2010 version. Ill have a look at the code, but im guessing i wont get very far! lol

Thanks

**ragulduy** · 04-09-2014, 06:42 AM

Ok, so in 2010 you should be able to use
.displayformat
rather than the code I posted, unfortunately I'm not familiar with the .displayformat property, I'll see if someone else can help.

**romperstomper** · 04-09-2014, 09:14 AM

Which sheets in each workbook? You basically just need to test if the DisplayFormat.Font.Color = vbRed (or whatever colour you are interested in).

**romperstomper** · 04-09-2014, 09:21 AM

Here's a rough example:

Please Login or Register  to view this content.

**Barking_Mad** · 04-09-2014, 09:56 AM

Hi Thanks Romperstomper - i actually need to to work as a private sub, so it runs on each worksheet (they'll all be generated from a template). Do i just use the location of the workbook (ie. C/Desktop/Workbook2.xlsx) in place of "Workbook2.xlsx" ?

**romperstomper** · 04-09-2014, 10:06 AM

No - the workbook has to be open, so you refer to it only by name, not path.

**Barking_Mad** · 04-09-2014, 10:20 AM

Originally Posted by romperstomper

No - the workbook has to be open, so you refer to it only by name, not path.

Ahh ok it cant be open due to various reasons i wont bore you with... so if i could instead put that data in another sheet (say "Names" in the same workbook that the comments are to be placed in - what would the code look like then?

**romperstomper** · 04-09-2014, 10:32 AM

Assuming the code is in the same workbook, change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

**Barking_Mad** · 04-09-2014, 10:37 AM

Originally Posted by romperstomper

Assuming the code is in the same workbook, change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

Thanks so much, that works spot-on. Many thanks!

**romperstomper** · 04-09-2014, 10:41 AM

Glad to help.

**Barking_Mad** · 04-09-2014, 10:53 AM

Oh just noticed one thing - it runs as desired the first time - but when it runs again (on unchanged data) I get an error:

Application-defined or object-defined error.

on code in bold:

Please Login or Register  to view this content.

**romperstomper** · 04-09-2014, 10:57 AM

I did say it was rough.

Please Login or Register  to view this content.

Note: this will replace any existing comment text. Not sure if you want that or to add it.

**Barking_Mad** · 04-10-2014, 03:33 AM

Originally Posted by romperstomper

I did say it was rough.

Please Login or Register  to view this content.

Note: this will replace any existing comment text. Not sure if you want that or to add it.

Thanks

Your rough is better than my not at all! I can understand vb when i see it coded, but actually trying to get any of it to stick in my mind is like throwing jelly at a wall! Replacing comments will be fine

**romperstomper** · 04-10-2014, 04:13 AM

We all started somewhere - perhaps this will be the beginning of your journey into VBA madness.

Returning cell values in a comment based on values in another workbook

LinkBack

Thread Tools

Rate This Thread

Display

Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Re: Returning cell values in a comment based on values in another workbook

Thread Information

Users Browsing this Thread

Similar Threads

Determine a set of values to use based on another cell's data, AND returning a blank cell

Returning a value to a single cell based on multiple values elsewhere.

Returning a Column Header based on max values in a row, that update when values change

Auto Populate Comment Based on Cell Values (Big Project - Need a GURU!!!)

Returning Values to a specific cell based on multiple conditions

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions