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.

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

<tbody>
</tbody>

sheet2
O	R
WR#	MC#
2571755 <tbody> </tbody>	MC-136244 <tbody> </tbody>
3616397 <tbody> </tbody>	MC-209089 <tbody> </tbody>
3860962 <tbody> </tbody>	MC-185865 <tbody> </tbody>
3894677 <tbody> </tbody>

<tbody>
</tbody>

 

[mole999]

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