Compare two cell and do green color if 80% text matches

[aayaanmayank]

Hi Can any one help me on this? i have below data which need to compare with the next row and give green color if 80% text matches

image removed

i have written below code but excel is getting hang whenever i run the Macro

sub colorr()

Set shgroup = ThisWorkbook.Worksheets("Grouping Data_Underwriters")


lastrow1 = shgroup.Range("B" & Rows.Count).End(xlUp).Row


Range("B2").Select


For U = 2 To lastrow1

Set MYNAME2 = Cells(U, "B")
Set MYNAME3 = Cells((U + 1), ("B"))
MY = Left(MYNAME2, Len(MYNAME2) * 0.8)

x = Len(MY)
y = Left(MYNAME3, x)
If y = MY Then


shgroup.Cells(U, "B").Interior.Color = vbGreen
shgroup.Cells(U + 1, "B").Interior.Color = vbGreen


End If


Next U

END SUB

 

[footoo]

Just want to understand and if you can check that why is it doing green color on below name even text is not matching 80% text
[TABLE="class: cms_table, width: 235"]
<tbody>[TR]
[TD]JOHNSONNOIL[/TD]
[/TR]
[TR]
[TD]JOHNSONNOILANUNITEDGENERALPARTNERSHIP[/TD]
[/TR]
</tbody>[/TABLE]

The macro takes 80% of JOHNSONOIL and compares with the next cell - so in this case it matches.

That's why I asked if your macro does what you want - it presumably doesn't.
How do you want the comparison done?

 

[aayaanmayank]

i think, i am good too go because its just one case. by any which ways we have either compare first row to second row or second to first row. if we follow both the parameters then code will be large and there is maximum possibility then excel gets stuck and stop working

 

[footoo]

i think, i am good too go because its just one case. by any which ways we have either compare first row to second row or second to first row. if we follow both the parameters then code will be large and there is maximum possibility then excel gets stuck and stop working

It's still not clear to me exactly what the criteria are for coloring the cells.
Following both parameters would not be a problem but don't see what that would achieve - your JOHNNSONOIL example would still get colored.

 

[Peter_SSs]

Could we have 10-15 rows of varied dummy sample data, the expected results and explanation in relation to that sample data?

 

[aayaanmayank]

it will be maximum go till 6-to 7 rows.
[TABLE="width: 156"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]402445477567[/TD]
[TD]8HEALTHSOUTH CENTER PARHOI[/TD]
[/TR]
[TR]
[TD]402445477567[/TD]
[TD]9DIAGNOSTIC HEALTH OF WESTABC[/TD]
[/TR]
[TR]
[TD]402445477567[/TD]
[TD]3DIAGNOSTIC HEALTH MRI OF LLCPOI[/TD]
[/TR]
[TR]
[TD]402445477567[/TD]
[TD]8OUTPATIENT DIAGNOSTIC CENTER OF EHOI[/TD]
[/TR]
[TR]
[TD]402445477567[/TD]
[TD]8HEALTHSOUTH CENTER PARHOI[/TD]
[/TR]
[TR]
[TD]402445477567[/TD]
[TD]8HEALTHSOUTH DIAGNOSTIC CENTER INCHOI[/TD]
[/TR]
</tbody>[/TABLE]

 

[footoo]

But what result do you want and why? What about the JOHNNSOIL example?
Should the data be sorted before doing the comparison?