problem comparing cells based on a condition

[mehdoush]

hello guys

i'm using a table like this one :


A | B

Part1 4512654_04
Part2 4512654_03
Part3 4512444_01
Part4 4512654_04
.
.
PartN 4512654_02

I want to check if the 6 last numbers of a ref. number (Column B) are similar with the last 6 numbers of another ref. number.

for example: Part1 | "654_04" and Part4 "654_04" ; if so color them in red.

Thanks in advance for your help
regards

[mdmackillop]

[vba]
Sub Test()
Dim rng As Range, cel As Range, i As Long
Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
For Each cel In rng
For i = 2 To rng.Cells.Count
If Right(cel, 6) = Right(rng(i), 6) And cel.Address <> rng(i).Address Then
cel.Interior.ColorIndex = 3
rng(i).Interior.ColorIndex = 3
End If
Next
Next
End Sub
[/vba]

[mehdoush]

Thanks a lotmdmackillop
regards

[mehdoush]

hey mdmackillop, hey everyone

what if i wanted to compare like so :
the last three chars before "_" sign

for example: Part1 | "654"" and Part4 | "654" ; if so color them in red.

your help is appreciated
regards

[mdmackillop]

You can use Mid and Find to find and return strings from within strings. Check these out in the Help file.

[mehdoush]

that's ok mdmackillop, but i'm not a specialist, some help will be appreciated...

i can make it but it won't work, i always get errors, you see...

thanks in advance

[mdmackillop]

Please post your code.