How to group rows by value and comapre two columns in two sheet and highlight

hello,
I am very new to vba. i was working on this project but got stuck and couldnt figure things out. i would really appreciate your help.
There is one workbook with two sheet sheet1 and sheet2. I am working on sheet1 column b (wr#), c (mc#), e(address) g(comment) and sheet2 column o(wr#) and r(mc#). sheet1 have duplicate wr# and mc# but sheet2 values are unique. I have figure some things out, but stuck with grouping and comparing. you can see the example

here is the list of things i wanna do.

1. all duplicate mc# are grouped together all the rows, orange colored.
2. in group, when column g is empty, column e should be green for whole group. even if one cell in g is have value, the whole group would be normal orange colored.
3. one or group of mc# is green when common with sheet2 mc#.
4. one or group of mc# is yellow when it is not common with sheet2 mc#.
5. one mc# is red in sheet2 when its not in sheet1.
6. we will consider and select wr# only when mc# is empty,
7. when mc# is empty group all duplicate wr# and color blue all rows .
8 in group, when column g is empty, column e should be green for whole group. even if one cell in g is have value, the whole group would be normal blue colored.
9. one or group of wr# is green in sheet1 when common with sheet2 wr#.
10. one or group of wr# is yellow in sheet1 when it is not common with sheet2 wr#.
11. one wr# is red in sheet2 when its not in sheet1.


sheet1
B C E G
WR# MC# Add Comm
2571755 MC-136244 HIMROD ST-
2571755 MC-136244 HIMROD ST-
3517927 MC-209089 VAN DAM ST ok
3616397 MC-229268 QUEENS PLZ
3616397 MC-229268 QUEENS PLZ ok
3951681 218 ST
3894677 DITMARS ST
3894677 DITMARS ST


sheet2
O R
WR# MC#
2571755 MC-136244
3616397 MC-209089
3860962 MC-185865
3894677