Compare 2 worksheets, delete dupes and group by back fill color using macro

I'm working on a project new to the VBA code to latest version of Excel. It's been a long time since I've written any code and trying to automate one of my daily tasks at my work. Have a spreadsheet with 9 columns of data with varying rows. I have a current worksheet I work from daily and need to to compare with new updated worksheet with new data. I have to remove the items from the current sheet which are not listed on the new sheet moved to a 3rd worksheet. Delete the duplicates which shows up on the new daily data - keeping my existing work and add the new data to my existing work then group the various sections highlighted in background fill color.

Is this possible? I have recorded a macro to get the initial leg work on it but having problems getting it to work properly with a variable amount of rows changing on a daily basis

Any thoughts or ideas would be greatly appreciated to automate this very time consuming task.
-Larry-

Here is what I do manually:
I open my current worksheet I work on and update on daily basis, I align my data in column A to left justified.
Open the daily report for today's work cut and paste at the bottom of my current worksheet.

Manually scan through and see what data in column A is left justified and not duplicated, cut that data and paste into my ongoing list data on worksheet 3.
After going through the whole list, remove the duplicates out of the entire row if column A is duplicated.
Take my end result and background fill by level's of work, Yellow, light red, Red, no color fill, light purple, and green and group them all together.

Hi jousterlj and welcome to the forum.

I've solved many "daunting tasks" by doing a little piece at a time. VBA is very robust and can accomplish what you describe above but there are different ways to attack the problem(s).

One hint in my VBA which I use in almost all code is determining how many rows of data I'm dealing with. I use the following code to determine it.

Please Login or Register  to view this content.

I've also used the following line of code to open a file. You might use this for opening your daily file.

Please Login or Register  to view this content.

Followed by

Please Login or Register  to view this content.

My suggestion is to record little pieces of your process and understand what the recorded code is doing and then generalize it to make it work for any file or any number of rows.