VBA look if value in array is in range-CodePudding

i create an array with the information above.

Dim arr As Variant
arr = ActiveSheet.Range("F2:F" & Range("F1").End(xlDown).Row).Value

Now i want to look in a range, if a value from array is in the range. if yes than change the color for this matching cell.

Set ws = Workbooks("test.xlsx").Worksheets("test")

For i = 2 To Range("A2").End(xlDown).Row

    For j = 1 To UBound(arr, 1)
    
        If arr(j) = Cells(i, 7).Value Then Cells(i, 7).Interior.color = RGB(180, 198, 231)
            
    Next
    
Next

i get a type mismatch error when starting the second loop

CodePudding user response：

If your code is in the same workbook as the workbook in which you want to search, then the workbook test.xlsx must be saved as test.xlsm .

CodePudding user response：

arrays built from ranges are 2D, even if only 1 column of data. The following Works:

Sub Test()

    Dim arr As Variant
    Dim WS As Worksheet
    Dim I As Long
    Dim J As Long
    
    arr = ActiveSheet.Range("F2:F" & Range("F1").End(xlDown).Row).Value
    'Set WS = Workbooks("test.xlsx").Worksheets("test") 'Your Worksheet
    Set WS = ThisWorkbook.Worksheets(1) 'My worksheet
    
    For I = 2 To Range("G2").End(xlDown).Row
        For J = 1 To UBound(arr, 1)
            If arr(J, 1) = Cells(I, 7).Value Then Cells(I, 7).Interior.Color = RGB(180, 198, 231)
        Next
    Next
    
End Sub