Create report compareing 2 Sheets

If you run a macro, stored in your Personal.xlsb, on the sheets, it does not care about what version the sheets are so long as their names are the same, e.g., sheet1 and sheet2.

The sheets to be compared have different names daily. I always compare today with yesterday, for examle (sheetnames: 120509 and 110509)
I am able to locate the sheets with the names of todays and yesterday dates.
But how do i do the marcro to find who is in sheet1 and not in sheet2 and who is in sheet2 but not in sheet1?
I can only find macros that search for dublicates, but that does not help me.

Sub compare_two_column1()



lastrow& = Sheets(1).Cells.find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
i = 1
Do Until lastrow = i
Tag = Worksheets(1).Cells(i, 1)

Set c = Worksheets(1).Range("b1", Worksheets(1).Cells(lastrow, 2)).find(Tag, LookIn:=xlValues)
If c Is Nothing Then
Worksheets(1).Cells(i, 1).Interior.Color = vbYellow
End If

i = i + 1


Loop
End Sub

Sub compare_two_column2()



lastrow& = Sheets(1).Cells.find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
i = 1
Do Until lastrow = i
Tag = Worksheets(1).Cells(i, 2)

Set c = Worksheets(1).Range("a1", Worksheets(1).Cells(lastrow, 1)).find(Tag, LookIn:=xlValues)
If c Is Nothing Then
Worksheets(1).Cells(i, 2).Interior.Color = vbYellow
End If

i = i + 1


Loop
End Sub



compare two column and put some color on diffference if it not find

wow, that looks good. I have the names in 2 different sheets, but i think i can figure that out.
Thank you verry much for your response!
I'm back to testing

i just find for you this function

ActiveWindow.SelectedSheets

so u can select two sheet from sheet tab (tab name) at the bottom with ctrl button and mouse. So you don't to enter in the vba to change sheet name

sheet1_name = ActiveWindow.SelectedSheets.Item(1).CodeName
sheet2_name = ActiveWindow.SelectedSheets.Item(2).CodeName

just tested the code you gave me, works like hell

Sub compare_two_sheets()
'mark green who is new on the list (sheet1)
lastrow1& = Sheets(1).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
lastrow2& = Sheets(2).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
i = 2
Do Until lastrow1& = i
Tag = Worksheets(1).Cells(i, 2)
Set c = Worksheets(2).Range("b2", Worksheets(2).Cells(lastrow2&, 2)).Find(Tag, LookIn:=xlValues)
If c Is Nothing Then
Worksheets(1).Cells(i, 2).Interior.Color = vbGreen
End If
i = i + 1
Loop

'mark red who dropped out the list (sheet2)
r = 2
Do Until lastrow1& = r
Tag = Worksheets(2).Cells(r, 2)
Set c = Worksheets(1).Range("b2", Worksheets(1).Cells(lastrow1&, 2)).Find(Tag, LookIn:=xlValues)
If c Is Nothing Then
Worksheets(2).Cells(r, 2).Interior.Color = vbRed
End If
r = r + 1
Loop
End Sub

Thank you, you saved my night! The "selctedsheet" is another big help.