If 1st 3 Digits of Column A match First 3 Digits Of Column B highlight both cell with a color

[Pankti]

I have a sheet with many numbers, and manually finding matching numbers is hard, so I want a solution to this.
If 1st 3 Digits of Column A match the First 3 Digits Of Column B highlight both cells with a color.
something like this:

	A	B
1​	111.45	4564
2​	34532	111.75
3​	565	5435

in this example, A1 and B2 should have the same color fill or the same color text.
something that visually tells me these numbers are a match.

would appreciate the help
thank you

 

[SeniorNewbie]

Hi Pankti, try this (code to a module)

Sub Highlight()
Dim i As Long, j As Long, r As Long

r = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To r          'if there's no headline start with 1
    For j = 2 To r
        If Left(Cells(i, 1), 3) = Left(Cells(j, 2), 3) Then
            Cells(i, 1).Interior.ColorIndex = 6
            Cells(j, 2).Interior.ColorIndex = 6
        End If
    Next j
Next i

End Sub

Have fun
Senior Newbie

 

[SeniorNewbie]

This second version is a little bit more multi colored and the "couples" are easier to find: sam value = same color

Sub Highlight()
Dim i As Long, j As Long, l As Long
Dim r As Integer, g As Integer, b As Integer

l = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

r = 1: g = 85: b = 170
For i = 2 To l
    For j = 2 To l
        If Left(Cells(i, 1), 3) = Left(Cells(j, 2), 3) Then
            Cells(i, 1).Interior.Color = RGB(r, g, b)
            Cells(j, 2).Interior.Color = RGB(r, g, b)
        End If
    Next j
    r = r + 40
    If r > 250 Then
        r = r - 250
    End If
    g = g + 40
    If g > 250 Then
        g = g - 250
    End If
    b = b + 40
    If b > 250 Then
        b = b - 250
    End If
Next i

End Sub

Sub Reset()
ActiveSheet.Cells.Interior.Color = xlNone
End Sub

Have fun!

 

[Fuji]

Conditional Formatting.
1) Select A2:A100 Formula:= =AND(A2<>"",ISNUMBER(MATCH(LEFT(A2,3)&"*",B$2:B$100,0)))
2) Select B2:B100 Formula:= =AND(B2<>"",ISNUMBER(MATCH(LEFT(B2,3)&"*",A$2:A$100&"",0)))

 

[mumps]

What happens if you have 123.45 in column A and 12.345 in column B? Would this be a match?

 

[SeniorNewbie]

Hi mumps
in my VBA version it would not be a match, because 123 and 12. is not equal.

 

[mumps]

We would need a response from @Pankti

 

[SeniorNewbie]

Sorry, mumps - that (@Pankti) wasn't clear ...

 

[mumps]

No need for an apology. We'll wait and see.

 

[Pankti]

This second version is a little bit more multi colored and the "couples" are easier to find: sam value = same color

Sub Highlight()
Dim i As Long, j As Long, l As Long
Dim r As Integer, g As Integer, b As Integer

l = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

r = 1: g = 85: b = 170
For i = 2 To l
    For j = 2 To l
        If Left(Cells(i, 1), 3) = Left(Cells(j, 2), 3) Then
            Cells(i, 1).Interior.Color = RGB(r, g, b)
            Cells(j, 2).Interior.Color = RGB(r, g, b)
        End If
    Next j
    r = r + 40
    If r > 250 Then
        r = r - 250
    End If
    g = g + 40
    If g > 250 Then
        g = g - 250
    End If
    b = b + 40
    If b > 250 Then
        b = b - 250
    End If
Next i

End Sub

Sub Reset()
ActiveSheet.Cells.Interior.Color = xlNone
End Sub

Have fun!

sorry, but I am new to all these formula things in Excel.
can you help me where to apply this code in Excel?
or can you apply it to my sheet for my understanding?
thank you

Book1
	A	B
1	235.42	445.63
2	489.46	376.53
3	295.56	490.05
4	589.59	892.3
5	1246.53	115.63
6	345.34	235.33
7	376.6	3905.54
8	112.05	1246.53
9	892.3	165.99
10	115.43	454.35
Sheet1