Comparing List

[everscern]

Private Sub Worksheet_Change(ByVal Target As Range) 
Application.EnableEvents = False 
With Target.Cells(1,1) 
   If Not Intersect(.Cells, Range("b3:b9")) Is Nothing Then 
      Range("b24") = Empty      
      For i = 3 To 9 : txt = txt & Cells(i,"b").Value & "_" : Next 
      For Each r In Range("s3",Cells(3,Columns.Count).End(xlToLeft)) 
         For i = 0 To 6 : txt2 = txt2 & r.Offset(i).Value & "_" : Next 
         If txt = txt2 Then 
            Range("b10").Value = r.Offset(7).Value 
            Exit For 
         End If 
      Next 
   ElseIf Not Intersect(.Cells,Range("b17:b23")) Is Nothing Then 
      Range("b24") = Empty 
      For i = 17 To 23 : txt = txt & Cells(i,"b").Value & "_" : Next 
      For Each r In Range("s17",Cells(17,Columns.Count).End(xlToLeft)) 
         For i = 0 To 6 : txt2 = txt2 & r.Offset(i).Value & "_" : Next 
         If txt = txt2 Then 
            Range("b24").VAlue = r.Offset(7).Value 
            Exit For 
         End If 
      Next 
   End If 
End With 
Application.EnableEvents = True 
End Sub

So far, I've up to this code which works. But.. it does not compare with column T onwards.

\1

Can anyone help me pls? I'm really at wits end. Thanks.

[Myles]

I'd love to help but the structure of your code is hard to follow-particularly when some loops are condensed to a single line followed by a structured conventional loop.

[everscern]

Thanks for your response.

Could u propose another macro?
I'm really having a hard time with this.

My purpose is to compare column B with column S, T etc. Upon comparison, the output will appear in cell B24. To complicate things, I hope to make this macro automatic thru worksheet change. As worksheet change cannot appear more than once, it makes this macro more tedious.

[everscern]

'created by acw
Private Sub Worksheet_Change(ByVal Target As Range) 
    Application.EnableEvents = False 
    With Target.Cells(1,1) 
        If Not Intersect(.Cells, Range("b3:b9")) Is Nothing Then 
            Range("b24") = Empty 
            For i = 3 To 9 : txt = txt & Cells(i,"b").Value & "_" : Next 
                For Each r In Range("s3",Cells(3,Columns.Count).End(xlToLeft)) 
                    For i = 0 To 6 : txt2 = txt2 & r.Offset(i).Value & "_" : Next 
                        If txt = txt2 Then 
                            Range("b10").Value = r.Offset(7).Value 
                            Exit For 
                        End If 
                    txt2 = "" 
                Next 
                ElseIf Not Intersect(.Cells,Range("b17:b23")) Is Nothing Then 
                    Range("b24") = Empty 
                    For i = 17 To 23 : txt = txt & Cells(i,"b").Value & "_" : Next 
                        For Each r In Range("s17",Cells(17,Columns.Count).End(xlToLeft)) 
                            For i = 0 To 6 : txt2 = txt2 & r.Offset(i).Value & "_" : Next 
                                If txt = txt2 Then 
                                    Range("b24").VAlue = r.Offset(7).Value 
                                    Exit For 
                                End If
                              txt2 = "" 
                            Next 
                        End If 
                    End With 
                    Application.EnableEvents = True 
                End Sub

thought of sharing with u guys, because i finally got the code for my above question. Thanks for providing any help !