Excel VBA highlight cell where Rows and Column Match with 2 sheets

Question

Hi All, I have two columns in sheet1 with multiple rows and the same columns in sheet2 but in sheet2 values of 2nd column becomes column headings, I want to highlight the cell in sheet2 where values match from sheet1. Thanks in advance, i hope it makes clear. Screenshots also attached. i write the following code but this is not working correctly. Thanks for you help

Public Sub test3()

Dim rng As Range
Dim aNumber As Range
Dim bNumber As Range
Dim rng2 As Range
Dim LastColumn As Long
Dim iRow As Long
Dim iCol As Long
Set rng = Sheets("Sheet2").Range("B2:B" & Range("B" & Rows.Count).End(xlUp).row)
LastColumn = Sheets("Sheet2").Range("D1").CurrentRegion.Columns.Count
Set rng2 = Sheets("Sheet2").Range(Cells(1, 1), Cells(1, LastColumn))
'MsgBox rng2.Address
For iRow = 2 To 6 'this i need last row count
For iCol = 2 To 6 ''this i need last row count
    Set aNumber = Sheets("Sheet1").Cells(iRow, 1) 'Row, Column Searching A
    Set bNumber = Sheets("Sheet1").Cells(iCol, 2) 'Row, Column Searching B

    If Application.WorksheetFunction.CountIf(rng, aNumber) > 0 Then
    If Application.WorksheetFunction.CountIf(rng2, bNumber) > 0 Then

        ColNum = Application.WorksheetFunction.Match(bNumber, rng2, 0)
        RowNum = Application.WorksheetFunction.Match(aNumber, rng, 0)
        'MsgBox (RowNum + 1)
        'MsgBox (ColNum)
        Sheets("Sheet2").Cells(RowNum + 1, ColNum).Interior.Color = vbGreen
    Else
        'MsgBox aNumber & " does not exist in range " & rng.Address
    End If
    End If
Next iCol
Next iRow
End Sub

In your first table, is the second row supposed to be Language 2 | Jave? Or indeed Language 1 | Jave? — BruceWayne
** Language 1 Java, Rows can be duplicate but if you want unique values when combining we can add 3rd column also — Dani
Create two named ranges on your Sheet1, the first for the Language 1, Language 2, etc list (I named it listLanguageHeaderA and the second for the the Java, Jave, etc list (I named it listLanguageHeaderB). Then use this conditional format formula in cell B2 on Sheet2: =COUNTIFS(listLanguageHeaderA,$A2,listLanguageHeaderB,B$1)>0 and apply the conditional formatting to range: =$B$2:$G$7 — tigeravatar

BruceWayne BruceWayne · Accepted Answer · 2018-10-18T20:21:26

With a helper column C (=CONCAT($A2,$B2)), you can do this with Conditional Formatting.

Set up your helper column:

Then, set up a new Conditional Formatting rule.

Rule:

=IF(ISERROR(INDEX($C$3:$C$8,MATCH(CONCAT($E3,F$2),$C$3:$C$8,0))),FALSE,TRUE)

Applies To Range:

=$F$3:$K$8

You may have to tweak those ranges, but that worked for me:

Excel VBA highlight cell where Rows and Column Match with 2 sheets

1 Answers