for loop

[montecarlo2012]

Hi.
I Hope to be lucky and get some help

Sub D_M()
      Dim X As Range, Y As Range
            For Each X In Range("B2:F2")
                  Set Y = Range("M2:M37").Find(X, LookIn:=xlValues, lookat:=xlWhole)
                           If Not Y Is Nothing Then
                                     Y.Interior.ColorIndex = 6
                           End If
            Next X
End Sub

Sub D_N()
      Dim X As Range, Y As Range
            For Each X In Range("B3:F3")
                  Set Y = Range("N2:N37").Find(X, LookIn:=xlValues, lookat:=xlWhole)
                           If Not Y Is Nothing Then
                                     Y.Interior.ColorIndex = 6
                           End If
            Next X
End Sub
Sub D_o()
      Dim X As Range, Y As Range
            For Each X In Range("B4:F4")
                  Set Y = Range("O2:O37").Find(X, LookIn:=xlValues, lookat:=xlWhole)
                           If Not Y Is Nothing Then
                                     Y.Interior.ColorIndex = 6
                           End If
            Next X
End Sub

I upload this just to illustrate the logic I am trying
I want to read my array B2:F64 and match the M2:AZ37
I think the 3 codes make more sence
read B2:F2 and check if on Column M there are matches and highlight them
read B3:F3 and check if on column N there are matches and hightlight them
etc.
so I tried this

Sub MatchAndHighlight()
    Dim i As Long, j As Long
    Dim X As Range, Y As Range
    Dim col As String
    
    For i = 2 To 34
        For j = 13 To 25
            
            
            For Each X In Range("B" & i & ":F" & i)
                Set Y = Range(col & "m:" & col & "bb").Find(X, LookIn:=xlValues, lookat:=xlWhole)
                If Not Y Is Nothing Then
                    Y.Interior.ColorIndex = 6
                End If
            Next X
        Next j
    Next i
End Sub

but is working completely wrong
some help would be nice

 

[Jimmypop]

Hi there...

Maybe try...

It looks like the issue in your MatchAndHighlight code is that you're not setting the col variable to the correct column letter. You could modify your code like this to loop through each column from M to AZ, and check for matches with each cell in the B2:F64 range:

Sub MatchAndHighlight()
    Dim i As Long, j As Long
    Dim X As Range, Y As Range
    Dim col As String
    
    For i = 2 To 34
        For j = 13 To 52 ' loop through columns M to AZ
            
            col = Split(Cells(1, j).Address, "$")(1) ' get the column letter
            
            For Each X In Range("B" & i & ":F" & i)
                Set Y = Range(col & "2:" & col & "37").Find(X, LookIn:=xlValues, lookat:=xlWhole)
                If Not Y Is Nothing Then
                    Y.Interior.ColorIndex = 6
                End If
            Next X
        Next j
    Next i
End Sub

This code should loop through each cell in the B2:F64 range, and for each cell, search for a match in the corresponding column from M2 to AZ37. If a match is found, the cell in the M2:AZ37 range is highlighted in yellow.

Note that this code assumes that your data is laid out in a specific way, with the B2:F64 range and the M2:AZ37 range being the same size, and with the first row of the M2:AZ37 range containing the column headers. If your data is laid out differently, you may need to modify the code accordingly.

 

[montecarlo2012]

thank you jimmy I hope you around for my next challenge.

 

[Jimmypop]

Hi there.. Glad you got right and that we could assist and thanks for the feedback...