+ Reply to Thread
Results 1 to 21 of 21

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

  1. #1
    Registered User
    Join Date
    10-12-2015
    Location
    Denver, CO
    MS-Off Ver
    2010
    Posts
    8

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

    Hi All,

    Is it possible to capture text color, through VBA, when using a Vlookup? My code is below. Basically, it copies colored text from Cell B11 on Sheet1, which is a merged cell of B11:P18. That colored text is then pasted into Sheet15 into a single cell that is matched with one of a list of company names. It then adds a Vlookup into B11, which references a dropdown menu with the company names, to find the text that was previously copied. However, it only is able to bring the text back into cell B11 as Black, because of the Vlookup. I need that text to be colored when it is brought back over through the Vlookup. Any ideas? All help is appreciated!! Thank you!

    Please Login or Register  to view this content.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,798

    Re: 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
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

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

    try inserting the following:

    Please Login or Register  to view this content.
    Ray
    Last edited by Raymundus; 01-20-2016 at 06:18 PM.

  4. #4
    Registered User
    Join Date
    10-12-2015
    Location
    Denver, CO
    MS-Off Ver
    2010
    Posts
    8

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

    Thank you, Ray!

    I inserted your code underneath -Dim X as Integer- and it is giving me the Runtime error 1004- Method 'Range' of object '_Worksheet' failed. I'm not great with VBA and this isn't my original code, so apologies if I should be able to figure that error out and cant!

    Thoughts?
    Thank you again!

  5. #5
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

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

    Colleen, Apologies... Just read through your initial post again and realized you need B11 font to be the same color as the cell pointed to by the vlookup, and not the same color as the Pasted Text. So you may scrap my code anyhow.

    You need something a little more complex, like a function that will find the cell pointed to by the vlookup and capture the font color index there. That will take me a little time tomorrow. Apologies for the confusion.

  6. #6
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

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

    Colleen, Apologies... Just read through your initial post again and realized you need B11 font to be the same color as the cell pointed to by the vlookup, and not the same color as the Pasted Text. So you may scrap my code anyhow.

    You need something a little more complex, like a function that will find the cell pointed to by the vlookup and capture the font color index there. That will take me a little time tomorrow. Apologies for the confusion.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,798

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

    Maybe something like:

    Please Login or Register  to view this content.

    Regards, TMS

  8. #8
    Registered User
    Join Date
    10-12-2015
    Location
    Denver, CO
    MS-Off Ver
    2010
    Posts
    8

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

    Hi TMS,

    Thank you for the code! I am getting a "Copy Method of Range class failed" error, with the error highlighted on the line "Sheets ("PVAData").Range("B" & x).Copy Range ("B11").

    I tried switching this to the Range ("B11:P18"), which is the range of the big merged box of cells to which I want the text from the PVAData sheet to be copied, and this allows the code to run and properly pastes the text where I want it, but again, does not bring the color over

    I appreciate the help again!
    Colleen

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,798

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

    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

  10. #10
    Registered User
    Join Date
    10-12-2015
    Location
    Denver, CO
    MS-Off Ver
    2010
    Posts
    8

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

    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
    Last edited by Colleen1213; 01-26-2016 at 01:56 PM.

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,798

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

    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

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,798

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

    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

  13. #13
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,634

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

    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
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  14. #14
    Registered User
    Join Date
    10-12-2015
    Location
    Denver, CO
    MS-Off Ver
    2010
    Posts
    8

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

    Thank you TMS,
    It makes sense that the merged cell is the problem. I tried adding some of the copy/paste with the unmerge/merge code that you gave me, but my attempts at writing anything to match the Company name in cell A1 with the company name in Column A on the PVA data tab (so it knows which cell in column B on the PVA Data tab to copy) have not been so successful. Let me know if you come up with a way to incorporate this into the unmerge/copy/paste/merge code! I appreciate all of your help and effort on this
    Colleen

  15. #15
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,798

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

    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?

  16. #16
    Registered User
    Join Date
    10-12-2015
    Location
    Denver, CO
    MS-Off Ver
    2010
    Posts
    8

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

    yes! That's exactly what I'm hoping to do.

  17. #17
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,798

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

    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

  18. #18
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,798

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

    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
    Attached Files Attached Files

  19. #19
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,798

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

    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.

  20. #20
    Registered User
    Join Date
    10-12-2015
    Location
    Denver, CO
    MS-Off Ver
    2010
    Posts
    8

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

    Wow, I'm so impressed! Thank you TONS for all of your help! You are a lifesaver

  21. #21
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,798

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

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 05-30-2014, 10:24 AM
  2. Replies: 4
    Last Post: 12-29-2013, 11:41 PM
  3. Changing text color using vlookup: conditional formatting or VB?
    By kerryhaglund in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-07-2013, 08:01 PM
  4. Replies: 0
    Last Post: 09-26-2012, 01:08 PM
  5. Vlookup to copy the color of text in cells
    By gummi in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-23-2008, 07:21 AM
  6. [SOLVED] RE: Can't format cell color/text color in Office Excel 2003 in files .
    By albertaman in forum Excel General
    Replies: 0
    Last Post: 02-16-2006, 12:00 AM
  7. change text color based on adjacent cell text color
    By matthewst in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-01-2005, 03:49 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1