PDA

View Full Version : problem comparing cells based on a condition



mehdoush
06-04-2009, 09:49 AM
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
06-04-2009, 10:31 AM
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

mehdoush
06-04-2009, 01:44 PM
Thanks a lot mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87)
regards

mehdoush
06-05-2009, 12:50 AM
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
06-05-2009, 01:27 AM
You can use Mid and Find to find and return strings from within strings. Check these out in the Help file.

mehdoush
06-05-2009, 03:30 AM
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
06-05-2009, 03:52 AM
Please post your code.