Check 2 cells from source to master file, if match, highligh cell X in master

[excelnoob001]

Hi all,

Context: I have three error-checking columns in source file, worksheet "MrExcel". If there is a Y in any of the columns, goto columns A and B. If columns A and B matches with columns A and B of master file, worksheet "MrExcel", highlight cell A1 in that master file

Private Sub loopIfY()

    Dim lastRow As Long, i As Long
    
    lastRow = Cells(rows.Count, "M").End(xlUp).Row
    
    For i = 7 To lastRow 'checks from M7 to last row
        If Cells(i, "M").Value = "Y" Then 'if it has letter Y
            'code here
        End If
    Next i
    
    For i = 7 To lastRow 'checks from M7 to last row
        If Cells(i, "T").Value = "Y" Then 'if it has letter Y
            'code here
        End If
    Next i
    
    For i = 7 To lastRow 'checks from M7 to last row
        If Cells(i, "AA").Value = "Y" Then 'if it has letter Y
            'code here
        End If
    Next i
    
End Sub

An example: Let say in source file, worksheet "MrExcel", column M, row 8, (M8) there is a Y. Goto range A8 and B8 and check if they match range A8 and B8 of master file, worksheet "MrExcel".

Hope everything makes sense .
Any help or insight is greatly appreciated.
Thanks!

 

[mumps]

You mentioned column M. What are the other 2 error-checking columns in the source file?

 

[excelnoob001]

The other two are "T" and "AA".

 

[excelnoob001]

Ah sorry, I put the wrong comment in my code. It should say 'checks M7 to last row', 'checks T7 to last row', and checks 'AA7 to last row'.

 

[mumps]

Place this macro in the source file. Change the workbook name (in red) to suit your needs.

Sub HighlightCell()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, fnd As Range, x As Long, sAddr As String
    Set srcWS = ThisWorkbook.Sheets("MrExcel")
    Set desWS = Workbooks("[COLOR="#FF0000"]Master.xlsx[/COLOR]").Sheets("MrExcel")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = 13 To 27 Step 7
        Set fnd = Range(Cells(7, x), Cells(LastRow, x)).Find("Y", LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            sAddr = fnd.Address
            Do
                If Range("A" & fnd.Row) = desWS.Range("A" & fnd.Row) And Range("B" & fnd.Row) = desWS.Range("B" & fnd.Row) Then
                    desWS.Range("A" & fnd.Row).Interior.ColorIndex = 6
                End If
                Set fnd = Range(Cells(7, x), Cells(LastRow, x)).FindNext(fnd)
            Loop While sAddr <> fnd.Address
            sAddr = ""
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[excelnoob001]

If possible, can you comment each line of code and explain what it is doing?

Thank you so much!

 

[mumps]

I hope this helps.

Sub HighlightCell()
    Application.ScreenUpdating = False 'turns off screen refreshing, preventing screen flicker and speeds up the macro
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, fnd As Range, x As Long, sAddr As String
    Set srcWS = ThisWorkbook.Sheets("MrExcel") 'sets the source sheet
    Set desWS = Workbooks("Master").Sheets("MrExcel") 'sets the destination sheet
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'finds last used row in the source sheet
    For x = 13 To 27 Step 7 'loos through every 7th column starting in column M
        Set fnd = Range(Cells(7, x), Cells(LastRow, x)).Find("Y", LookIn:=xlValues, lookat:=xlWhole) 'searches for "Y" in each column
        If Not fnd Is Nothing Then 'if "Y" is found, executes next line of code
            sAddr = fnd.Address 'find the address of the found "Y"
            Do 'starts a loop to search fro all Y's in the column
                If Range("A" & fnd.Row) = desWS.Range("A" & fnd.Row) And Range("B" & fnd.Row) = desWS.Range("B" & fnd.Row) Then 'compares columns A and B of one sheet to the other sheet
                    desWS.Range("A" & fnd.Row).Interior.ColorIndex = 6 'if there is a match, colors cell in column A
                End If
                Set fnd = Range(Cells(7, x), Cells(LastRow, x)).FindNext(fnd) 'looks for next occurrence of "Y" in the column
            Loop While sAddr <> fnd.Address 'loops through the column until it reaches the first found "Y" and then exits the loop
            sAddr = ""
        End If
    Next x 'goes to next column
    Application.ScreenUpdating = True 'turns screen refreshing back on
End Sub

 

[excelnoob001]

Hi mumps,

I did a test run of your code by having M7 and down contain ALL Y. On the master file, it didn't highlight ALL of column A7 and down.

 

[mumps]

I tested the macro on some dummy data and it worked properly. The macro looks for an uppercase "Y" so make sure that is the case with your data. Also make sure there are no leading or trailing spaces to the left and right of the "Y". If you are still having problems, perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[excelnoob001]

Hi, mumps

So sorry for not pointing this out earlier, but the column A and B of my master file, worksheet "MrExcel" isn't in the same order as the column A and B of my source file, worksheet "MrExcel". For the rows of column A and B that did match, that specific row in column A of the master file did highlight.

For example, source file, worksheet "MrExcel" could go like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Hi[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Bye[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Love

[/TD]
[/TR]
</tbody>[/TABLE]


Master File, worksheet "MrExcel"
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Love[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Bye[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Hi[/TD]
[/TR]
</tbody>[/TABLE]


For this purpose, let's say Y was found for all those three rows.
Only the second row would be highlighted.
However, it wouldn't highlight rows 1 or 3.

Thank you kind sir, hope that clears everything up.