Match multiple column with another sheet in VBA

[rajesh2022]

Hi All

I need one more suggestion, (My earlier post was resolved, to continue with that)

Now i have 2 excel sheet as below :

Sheet 1
I have multiple column rows,

1. I need to compare Sheet 1 Column B cell value with Sheet 2 Column A cell Value, If match not found, we can go to next row,
2. If match found, then i need to check Sheet 1 column from C to last column (All columns cells) and compare with Sheet 2 row (Where match found) from B to last column cell and check each cell if there any match found. If match found, i need to highlight cell with color, if no match found then we can go to next row.

I have added sample file as well. In the file In Input sheet B6 ="LG_EF_Approvers" is exist in Compare Sheet A1, and when comparing E6 =LG_Users which exist in Compare Sheet F1. So hightligted in Red color.

Any suggestion would be great.

 

[Marc L]

Hi, you wrote « If match found, i need to highlight cell with color » but nothing is highlighted in sheet #2​

but all row is highlighted in sheet #1 ! So try to well explain in details in order there is no cloud, nothin' to guess …​

​

 

[rajesh2022]

I wanted to highlight only in Sheet #1.
Sheet # 2 used to cross check for match.

 

[Marc L]

More clear but you wanna to highlight just the matching cell or all the row ?​

​

 

[rajesh2022]

Ok, i got it. I think entire B column highlighted in color (That was confusing), i am so sorry.

I would like to compare Sheet #1(Input) with Sheet # 2(Compare Sheet), and highlight in Sheet #1 only (I have changed font color in red color, for Row number 6 and 14 in the Input sheet). I would like to change fore color to red if match found (Entire Row). If no match found, then we can ignore (no need to highlight).

 

[Marc L]

According to your attachment an Excel basics VBA demonstration for starters :​

Sub Demo1()
        Dim V(), R&, L&, W, X
    With Sheet2.UsedRange.Rows
        ReDim V(1 To .Count)
        For R = 1 To .Count:  V(R) = .Item(R).Value2:  Next
    End With
    With Sheet1.UsedRange.Rows
           .Font.ColorIndex = xlAutomatic
        For R = 2 To .Count
            W = .Item(R).Value2
        For L = 1 To UBound(V)
            X = Application.Match(V(L), W, 0)
            If IsNumeric(X(1)) Then If X(1) = 2 Then If Application.Count(X) > 1 Then .Item(R).Font.Color = vbRed: Exit For
        Next L, R
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[rajesh2022]

Its working thanks a lot.

 

[rajesh2022]

Sorry to continue in closed thread, one question is that possible to hightlight cell which is matching?

From the code which will highlight entire code if match found (Earlier i thought highlighting row will be better option)

If IsNumeric(X(1)) Then If X(1) = 2 Then If Application.Count(X) > 1 Then .Item(R).Font.Color = vbRed: Exit For

Can we change from .Item(R).Font.Color = vbRed to specific cell?

 

[Marc L]

Which 'specific cell' as several cells match when the entire row is highlighted ?​

​

 

[rajesh2022]

Whichever cells match, i would like to highlight,

 

[Marc L]

So for matching cells rather than a specific one my VBA demonstration revamped :​

Sub Demo1r()
        Dim V(), R&, L&, W, X
    With Sheet2.[A1].CurrentRegion.Rows
        ReDim V(1 To .Count)
        For R = 1 To .Count:  V(R) = .Item(R).Value2:  Next
    End With
    With Sheet1.UsedRange.Rows
           .Font.ColorIndex = xlAutomatic
        For R = 2 To .Count
            W = .Item(R).Value2
        For L = 1 To UBound(V)
            X = Application.IfError(Application.Match(V(L), W, 0), False)
            If IsNumeric(X(1)) Then If X(1) = 2 Then If Application.Count(X) > 1 Then _
                For Each X In Filter(X, False, False): .Cells(R, Val(X)).Font.Color = vbRed: Next: Exit For
        Next L, R
    End With
End Sub

You may Like it !
​

 

[rajesh2022]

Thanks it worked.