How to group rows by value and comapre and highlight

[fahadun]

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.

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]WR#[/TD]
[TD]MC#[/TD]
[TD]Add[/TD]
[TD]Comm[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2571755[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]MC-136244[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]HIMROD ST-[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2571755[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]MC-136244[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]HIMROD ST-[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3517927[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]MC-209089[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]VAN DAM ST[/TD]
[TD]ok[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3616397[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]MC-229268[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]QUEENS PLZ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3616397[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]MC-229268[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]QUEENS PLZ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3951681[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]218 ST[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3894677[/TD]
[TD][/TD]
[TD] DITMARS ST[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3894677[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD] DITMARS ST[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]sheet2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]O[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]WR#[/TD]
[TD]MC#[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2571755[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]MC-136244[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3616397[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]MC-209089[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3860962[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

MC-185865

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3894677[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

Your 3 identical posts were auto-moderated, you will have received a system email telling you so