Compare two sheets, Highlight rows in second sheet that does not appear in the first

Hi There.

I am trying to create a spreadsheet for scheduling purposes. I have been doing a lot of reading and have not come up with a solution. I admit I do not now much about VBA. Here is an outline of what I am trying to do. I do hope someone out there is able to help me.

1) Delete green rows in all sheets except "Machine All"
2) Highlight rows green that appear in other sheets but are not in "Machine All" based on contents of three cells.
3) Move green rows in all sheets, except "Machine All", to the top of that sheet.

Not sure if this is possible or how hard it maybe. I have tried a lot of VB code I have found and I have not been either able to get it working or I was not able to modify it to do what I needed. I have seen a lot of great answers in these forums and thought that this is my best chance at getting some help to figure this out.

I greatly thank anyone who is willing to help.

Triump

Well... That is great to hear.

I have attached a sample of my sheet.

So here is a little more info of what I am trying to do.

The first thing is to delete all rows that are colored green. Then we look at the first row from "Machine All". We then look at the column "Resource ID". Here is a break down of the Resource ID's and their corresponding sheets.

CNC MILL SML 1 = CNC MILL SML 1
CNC MILL LRG 1 = CNC MILL LRG 1
CNC LATHE 1 = CNC Lathes
CNC LATHE 2 = CNC Lathes
KEARNS = KEARNS
TOS SU125 = Tos
LATHE = LATHE
SMALL MILL = SMALL MILL
Cut =Cut

If the "Resource ID" is CNC MILL SML 1, we check to see if the row we are looking at exists on CNC MILL SML 1. If it does, we do nothing. If it do not. We copy that row to the bottom of CNC MILL SML 1 and highlight it yellow. I would like to check to see if the row exists on CNC MILL SML 1 by using the columns "Description", "Base_ID", "Part_ID" and "Drawing ID". If there is a row that is on "CNC MILL SML 1" but not on "Machine All" I would like to color it green and move it to the top.

I hope this makes some sort of sense.

Thank you very much for your help. It would be so helpfull to get this sheet working.

Triump

Wow. That code looks very nice. Great job. I would love to buy you a beer.

I did try the spread sheet. There are a few issues.

When I first run the macro it copies all the rows to the proper sheets but it highlights the first row on the second sheet green every time. After I change the newly added rows from yellow to no fill and run the macro again. It changes the rows green again. It should only change them green if a row appears on a sheet but does not appear on the master sheet.

I hope this is an easy fix.

Thank you for your help.

Triump

Ahh. I am sorry to hear that. Was hoping it wouldn't have been to much trouble for you. Your code looked very nice as well.

Hope it doesn't give you to much trouble. I appreciate your efforts.

It looks great. The only issue I see is the CNC Mill LRG 1 sheet. When I run the macro the first line is highlighted green and the other lines have no fill when they should have be highlighted yellow.

Aside from that it seems to work great.

Thank you very much.

I am very sorry. I mussed have messed something up. There is no issue with the second sheet. I just tried it again and it seems to be working great.

Thank you very much for your effort. This will be so helpful.

Thank you
Triump

I have two questions.

Do I need to change anything if I add a new column?

Right now the macro copies all the rows to the other sheets starting at row 7. How do I change it to start at row 6 like machine all?

Thanks

I am quite sure your rep was well deserved before now.

That doesn't seem to much to change. I ran the code. The rows are now copied to the other sheets starting at row 6 but the code will not do anything if I add another column to Machine All as well as all other sheets. If I delete the extra row it works fine.

I am adding a column between K and L.

If I remove the column I added, the macro works great. The only thing is it doesn't move the green rows to the top.

As for the greenies. I want to identify the orders that have been completed the day before. So if an order is on one of the sheets that is not on the master, then it must have been completed. Color it green. Then the next day, when I run the macro, I want it to delete the green rows and create new green rows that are on a worksheet but not on machine all. I was thinking that if we deleted the green rows first, then compared the sheets with machine all, we could then mark the new differences.

Not sure if that makes sense.

That worked perfectly. One thing I did notice is that when the macro highlights rows green on sheet, it also copies those same rows to Machine All. I am not sure if that really matters. I did try a few different things with it and it didnt seem to have an effect. I have uploaded the spreadsheet with data. If you look at the sheet next to machine all, it has no green rows. If you press the update button, the top four rows turn green but then if you check the bottom of machine all those rows are they as well in green.

I also setup a few other sheet that formats some of the data for printing. I am not sure if there is a better way of doing it. Would you mine taking a look at the extra sheets and letting me know if there is a better way of doing it?

Thanks
Triump

That is great. I cant tell you how helpful this is. I update schedules now for fun.

Can you recommend a book or resource to help me get started in VBA?

Thanks