Help required for a tricky problem(s)

If anyone can help with the following it would be great.

I have a spreadsheet which currently goes through a series of columns and filters out "unrequired" results (from about 24 sheets), then compiles a front sheet with the information I require in it.

I then manually import the previous weeks data, and manually compare 2 columns highlighting the results as per the previous week (ie I use light green = good, tan = bad)

Here is the problem, I have to pass this to colleagues of mine when I am on leave, and none of the them are able to run this process and they do not have the time to sit with me for training. Therefore I want to automate this next step. Here are my thoughts on what I need to do.

1) Find the previous XLS document (possibly using a dialogue?)
2) Import the front sheet into a new sheet
3) Compare the data from column A (field 8 onwards) to column A in the new sheet (again field 8 onwards) and if it exists anywhere in this column highlight it

Any help on any of this would be great.

Hi, please take a moment to read our forum rules and adapt your title accordingly.
Thx

Sorry didn't realise. I have amended the title