+ Reply to Thread
Results 1 to 30 of 30

copy background color from one sheet to another if text match

  1. #1
    Registered User
    Join Date
    09-28-2017
    Location
    Windhoek
    MS-Off Ver
    2010
    Posts
    14

    copy background color from one sheet to another if text match

    Hi All.
    I am not good at VBA so I need some help. I have gone through the forum but can't find an example that lead me in the right direction.

    I currently use vlookup to copy the text from sheet1 to sheet2. I would also like to copy the background color of the cells from sheet1 to sheet2 and not only the text which vlookup currently do for me.

    Thanks

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: copy background color from one sheet to another if text match

    Hi martin_

    Perhaps this will give you an idea...
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Registered User
    Join Date
    09-28-2017
    Location
    Windhoek
    MS-Off Ver
    2010
    Posts
    14

    Re: copy background color from one sheet to another if text match

    Sintek. Thanks.

    IT give an idea, but the content of sheet2 change daily and with info added and deleted and so does the order which this appear. That is the main reason for using vlookup. on the moment I manually copy the color to go with the text. It is not a big worhsheet with about 100 rows, but it takes tine that I don't really have.

  4. #4
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: copy background color from one sheet to another if text match

    Hi Martin,

    Welcome to the Forum.

    Is the background colour the same for each copied cell?

    Regards

    peterrc

  5. #5
    Registered User
    Join Date
    09-28-2017
    Location
    Windhoek
    MS-Off Ver
    2010
    Posts
    14

    Re: copy background color from one sheet to another if text match

    Hi Peter.

    No they are not the same. I am using about 5 different colors depending of the status.

  6. #6
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: copy background color from one sheet to another if text match

    Hi Martin,

    Use Conditional Formatting (cell = or cell contains) on Sheet2 and match the colours used on Sheet1 for each "status".

    Regards

    peterrc

  7. #7
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: copy background color from one sheet to another if text match

    what code are you using for your vlookup copy

  8. #8
    Registered User
    Join Date
    09-28-2017
    Location
    Windhoek
    MS-Off Ver
    2010
    Posts
    14

    Re: copy background color from one sheet to another if text match

    Hi Peter.

    Maybe I did not explain properly.

    The text I copy is comments to explain the status that the current job is in. example color red will be for parts to be ordered so that the job can be completed and the text that I will copy will be explain exactly what parts it is and the cost of the part and from where the parts are coming and when it should arrive.

    So the comments is different for each job. I am sure there should be some kind of code that can almost work the some as vlookup that also copy the background color and not just the text.

    Thanks

  9. #9
    Registered User
    Join Date
    09-28-2017
    Location
    Windhoek
    MS-Off Ver
    2010
    Posts
    14

    Re: copy background color from one sheet to another if text match

    Hi Sintek.

    I recorded a macro to do this an a few other sorting functions for me quickly to save some time.

    The vlookup part looks like this.

    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],'sheet1'!C[-10]:C,11,FALSE)"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L110"), Type:=xlFillDefault

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: copy background color from one sheet to another if text match

    I am sure there should be some kind of code that can almost work the some as vlookup that also copy the background color and not just the text.
    vLookup will not keep the format of retrieved data. It is "content only". As per peterrc suggestion...Conditional formatting...
    Why don't you upload a small sample so that we can see if we can assist.

    Perhaps you can use .Range.Find method VBA instead of vlookup and then copy text and format over...
    Last edited by sintek; 09-28-2017 at 07:50 AM.

  11. #11
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: copy background color from one sheet to another if text match

    Hi Martin,

    Forgive me, but I have to ask!
    If the text is (going to be) different in Sheet2 compared to Sheet1 why are you using VLOOKUP?

    Regards

    peterrc

  12. #12
    Registered User
    Join Date
    09-28-2017
    Location
    Windhoek
    MS-Off Ver
    2010
    Posts
    14

    Re: copy background color from one sheet to another if text match

    sheet2.JPGsheet1.JPG

    For some reason I do not manage to upload the file.

    The first image is sheet2 with my vlookup and the bottom one is sheet1. I use vlookup as sheet1 will represent all data and work from yesterday. I pull a report everyday from another program and add it in excel as sheet2. So I would like all the comments that I made the previous day to be copied to the new day's sheet so I can work on it. There will always be new data and some of the old completed data will disappear.

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: copy background color from one sheet to another if text match

    Can easily be accomplished with code....Upload a sample like such....
    To attach a file, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

  14. #14
    Registered User
    Join Date
    09-28-2017
    Location
    Windhoek
    MS-Off Ver
    2010
    Posts
    14

    Re: copy background color from one sheet to another if text match

    Thanks, I found it, lets hope it uploaded.
    Attached Files Attached Files

  15. #15
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: copy background color from one sheet to another if text match

    Hi Martin,

    To attach a file to your post,
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    Regards

    peterrc

  16. #16
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: copy background color from one sheet to another if text match

    Make use of this code instead of your vlookup
    Please Login or Register  to view this content.
    Attached Files Attached Files

  17. #17
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: copy background color from one sheet to another if text match

    Maybe:

    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    09-28-2017
    Location
    Windhoek
    MS-Off Ver
    2010
    Posts
    14

    Re: copy background color from one sheet to another if text match

    Thanks, that worked liked like a dream. Just before I ran into another problem which I see and don't know how to handle.
    I have uploaded the same file again, I just added a sheet. I normaly name my sheets by date. I have tried this now and does not get it right 100%.
    As I go through the month I assume sheet1 will get my previous days date and sheet2 my new days date.
    Attached Files Attached Files

  19. #19
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: copy background color from one sheet to another if text match

    I assume you do this daily....If so, this should work...
    Today Date as 28.09.17 and yesterday as 27.09.17
    Please Login or Register  to view this content.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    09-28-2017
    Location
    Windhoek
    MS-Off Ver
    2010
    Posts
    14

    Re: copy background color from one sheet to another if text match

    error.JPG

    Hi Sintek.

    I really appreciate your time and effort to help me to sort this out. Something is not right, I get an error. Yes I do this every day of the week. so on monday I will refer back to the previous friday.

  21. #21
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: copy background color from one sheet to another if text match

    monday I will refer back to the previous friday.
    Nope, my code won't work for this...Refers to today and yesterday...

    Edited...this will work..refers last sheet and 2nd last sheet
    Please Login or Register  to view this content.
    Last edited by sintek; 09-28-2017 at 10:38 AM.

  22. #22
    Registered User
    Join Date
    09-28-2017
    Location
    Windhoek
    MS-Off Ver
    2010
    Posts
    14

    Re: copy background color from one sheet to another if text match

    I do not mind to go into the code every day and do a date change. So if it is possible to put in today's date as my current sheet, and yesterday,s or 2 days back as previous sheet will work 100% for me. That possible?

  23. #23
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: copy background color from one sheet to another if text match

    I do not mind to go into the code every day and do a date change.
    Really !!! Macro's is supposed to make one's life easier....The amended code in post21 will solve your problem...

    Edit...the reason the above code (post 20)did not work is because you changed the code!!!!
    Last edited by sintek; 09-28-2017 at 11:05 AM.

  24. #24
    Registered User
    Join Date
    09-28-2017
    Location
    Windhoek
    MS-Off Ver
    2010
    Posts
    14

    Re: copy background color from one sheet to another if text match

    How do I mark this post as solved?

  25. #25
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: copy background color from one sheet to another if text match

    Hi Martin

    Does the code solve your query?... thanks for rep +
    Top of page, thread tools...mark as solved.

  26. #26
    Registered User
    Join Date
    09-28-2017
    Location
    Windhoek
    MS-Off Ver
    2010
    Posts
    14

    Re: copy background color from one sheet to another if text match

    Hi Sintek.

    Yes thanks if solve my query. Will this code still work if I skip a few days (4-5 days), like say there is a long weekend and I want to continue after that as normal on a daily basis?

  27. #27
    Registered User
    Join Date
    09-28-2017
    Location
    Windhoek
    MS-Off Ver
    2010
    Posts
    14

    Re: copy background color from one sheet to another if text match

    I have one more question. How can I adapt this code if I want to add another colum to my sheet that I want to copy daily?

  28. #28
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: copy background color from one sheet to another if text match

    Sure...
    have a look at this snippet of code...
    It looks for the Wip No in Column B and the copies the comment which is 7 Columns to the Right of B i.e. Column I
    Please Login or Register  to view this content.
    ' This refers 7 col's to right which is Column I
    Adapt by changing to suite your requirements

    Edit...
    Or do you mean wanting to copy two columns?

  29. #29
    Registered User
    Join Date
    09-28-2017
    Location
    Windhoek
    MS-Off Ver
    2010
    Posts
    14

    Re: copy background color from one sheet to another if text match

    Yes I want to copy 2 columns, So refers col 8 and copy to Column J, if will also look for the WIP No in column B.

  30. #30
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: copy background color from one sheet to another if text match

    Please Login or Register  to view this content.
    Last edited by sintek; 10-02-2017 at 08:29 AM.

+ 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. [SOLVED] Match value, take value 1's background/font color and put it in value 2's entire range
    By AnirudhC in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-27-2017, 10:14 PM
  2. Replies: 0
    Last Post: 06-24-2015, 06:26 AM
  3. Change cell background color based on another cells background color
    By Queo in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 06-10-2014, 05:28 AM
  4. Replies: 4
    Last Post: 12-29-2013, 11:41 PM
  5. How to add to macro & have checkbox match background color?
    By jgomez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-16-2012, 11:26 AM
  6. Match a cells background color to another cells background color
    By cmb3587 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-10-2011, 02:54 PM
  7. change text color based on background color
    By great.bean in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-05-2009, 07:07 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