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

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.

Hi Martin,

Welcome to the Forum.

Is the background colour the same for each copied cell?

Regards

peterrc

Hi Peter.

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

Hi Martin,

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

Regards

peterrc

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

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

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

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.

Thanks, I found it, lets hope it uploaded.

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

Maybe:

Please Login or Register  to view this content.

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.

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.

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?

How do I mark this post as solved?

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?

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?

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.