Need Text Color To Come Through With VLookup - can use VBA

In principle, I would think so. Instead of putting a VLOOKUP formula in the cell, you'd have to copy the cell itself. You know where it is because you can use Match to locate the row and you know which column the data is in.

Suggest you post a sample workbook with some typical data and the existing code.

Regards, TMS

try inserting the following:

Please Login or Register  to view this content.

Ray

Maybe something like:

Please Login or Register  to view this content.

Regards, TMS

The problem is that I have nothing to work with. I had to make a test harness and then try to adapt your code.

As far as I can see, it moves data B11 to Sheet15 and then it needs to copy data from PVData to B11.

If you post a sample workbook with some typical data, I will review the code and try to get it to work in a "live" environment.

Regards, TMS

Template2.xlsm

Thank you, TMS!
I've attached a sample workbook.

On the PVA Analysis Tab, Cell A1 is a dropdown with a list of companies. Basically, I want to be able to type into B11:P18 in various colors and have the text and text colors moved to/saved on the PVA Data tab in Column B next to the company referenced in Cell A1 on the PVA Analysis tab. Then, in order to switch back and forth between companies listed in the dropdown Cell A1 without losing the saved text, the original code brought in the Vlookup. However, that is where the text color was not pulling back over to the PVA Analysis tab, cells B11:P18. I've since changed the vlookup code to TMS's code above. It's called Sub "Updatenotes()"

I would hope to be able to switch between the companies listed in the dropdown Cell A1 without the losing color of the text.

Let me know if you need more clarification. The Sub "updatenotes()" is the one in question.

THank you again, I really appreciate the help
Colleen

Ok, the sample workbook explains a lot. Because I didn't know where you were starting, I assumed there were three worksheets; 1) start point, 2) where the data was copied and 3) where the cell with colours needed to end up. The other BIG complication is that you have a very big merged cell ... which is probably what causes the problem.

I think I need to take a little time to step through it ... it might be a simple resolution but give me a little while (unless someone else steps in)

Watch out for a PM from me.

Regards, TMS

OK, as I thought, the merged cell is the problem. You can't manually copy a single cell with formatting to a merged cell and preserve the formatting. Not sure why not, but I'm sure there's a good reason. And, if you can't do something manually, you can't do it in VBA either.

There is a workaround: unmerge the cells, copy the source to the unmerged target cell, and then merge the cells again.

This code demonstrates the principle but I'll need to come back to refine it to do what you want (Need to combine this with the earlier stuff)

Please Login or Register  to view this content.

Regards, TMS

slightly amended version of updatenotes

Please Login or Register  to view this content.

essentially you are copying the colorindex of the cell 1 space at a time

Let me know if you come up with a way to incorporate this into the unmerge/copy/paste/merge code!

What? You mean make it work in real life?

Let me be clear: when you change the company entry in cell A1, you want to lookup the notes. And, if you change the notes, you want to locate the record and copy the (updated) notes to the list?

OK, the GroupChange macro will look like this:

Please Login or Register  to view this content.

I need to work on the UpdateNotes module but that will also look up the row number in exactly the same way but the copy will go the other way.

A simple test routine looks like this:

Please Login or Register  to view this content.

I'll look at it again tomorrow.

Regards, TMS

OK, as promised, Part II ... UpdateNotes:

Please Login or Register  to view this content.

You will need to change the Worksheet Change event handler to monitor both cells A1 and B11:

Please Login or Register  to view this content.

If there is a risk that anyone might rename the tabs, it might be worthwhile changing the Code Names for the sheets to match the Sheet Names, although you can't have spaces.

You could then refer to, say, PVA_Analysis rather than Sheets("PVA Analysis") and PVAData rather than Sheets("PVAData"). Safer, but more readable than Sheet1 and Sheet15.

See the updated workbook example.

Note that I have "blocked" the Before Save event handler in this copy so that I can save it I've also anonymised the names mentioned in the code.

Regards, TMS

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

You're welcome. Thanks for the rep.