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]

Could do with conditional formatting :

=LEFT(A1,LEN(A1)*0.8)=LEFT(A2,LEN(LEFT(A1,LEN(A1)*0.8)))

 

[aayaanmayank]

Thanks but i need vba code

 

[footoo]

Your macro ran OK for me but don't know whether it does what you want :

Sub colorr()
Dim shgroup As Worksheet, lastrow1%, U%, MYNAME2 As Range, MYNAME3 As Range, x%, y$, MY$
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]

Here's some alternative code :

Sub colorr()
Dim rng As Range, cel As Range
With Worksheets("Grouping Data_Underwriters")
    Set rng = .Range([B2], .Range("B" & Rows.Count).End(xlUp))
End With
For Each cel In rng
    If Left(cel, Len(cel) * 0.8) = Left(cel(2), Len(Left(cel, Len(cel) * 0.8))) Then _
    cel.Resize(2).Interior.Color = vbGreen
Next
End Sub

 

[aayaanmayank]

while running this code, i am getting an error "Application- defined or object-defined error"

 

[aayaanmayank]

Yes its solving my purpose thanks alot, however can you advise why is it doing green color on below name even text is not matching 80% text
[TABLE="width: 235"]
<colgroup><col></colgroup><tbody>[TR]
[TD]JOHNSONNOIL[/TD]
[/TR]
[TR]
[TD]JOHNSONNOILANUNITEDGENERALPARTNERSHIP[/TD]
[/TR]
</tbody>[/TABLE]

 

[footoo]

while running this code, i am getting an error "Application- defined or object-defined error"

To what macro are you referring and on what line?
Have you tried both macros (posts #4 and #5 )?

 

[aayaanmayank]

i am referring last macro which is #5 that (Application- defined or object-defined error) error has been resolved .

 

[aayaanmayank]

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]