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]

Try:

Sub HighlightCell3()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, fnd As Range, x As Long, sAddr As String, Val As String
    Dim v1 As Variant, v2 As Variant, rngList As Object
    Set rngList = CreateObject("Scripting.Dictionary")
    Set srcWS = Sheets("MrExcel")
    Set desWS = Workbooks("Master.xlsx").Sheets("MrExcel")
    v1 = srcWS.Range("A7", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 27).Value
    v2 = desWS.Range("A7", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    For i = 1 To UBound(v2, 1)
        Val = v2(i, 1) & "|" & v2(i, 2)
        If Not rngList.Exists(Val) Then
            rngList.Add Key:=Val, Item:=i + 6
        End If
    Next i
    For i = 1 To UBound(v1, 1)
        Val = v1(i, 1) & "|" & v1(i, 2)
        If rngList.Exists(Val) And (v1(i, 13) = "Y" Or v1(i, 20) = "Y" Or v1(i, 27) = "Y") Then
            desWS.Cells(rngList(Val), 1).Interior.ColorIndex = 6
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[excelnoob001]

Hi Mumps, I just want to thank you for your patience with me.

I am new to VBA and to this forum, so thank you for bearing with my incompetence.

However, the above macro you sent me isn't highlighting any of the cells. I am at wits end with this; I can't figure out if there's a trouble on my end, or I did a bad job explaining it.

I have attached both the source and master file.

https://www.dropbox.com/s/itbcdcaqty8zqnt/Mr Excel Source.xlsx?dl=0 (source file)

https://www.dropbox.com/s/oej1zh49c6hadfv/Mr Excel Master.xlsx?dl=0 (master file)

I changed it so that if there is a Y for a specific column in the source file, highlight that in the corresponding column in the master file.

Thank you Mumps, God bless you.

 

[mumps]

Try this version in the Master. Change the source workbook name (in red) to suit your needs.

Sub HighlightCell3()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, fnd As Range, x As Long, sAddr As String, Val As String
    Dim v1 As Variant, v2 As Variant, rngList As Object
    Set rngList = CreateObject("Scripting.Dictionary")
    Set srcWS = Workbooks("[COLOR="#FF0000"]Mr Excel Source.xlsx[/COLOR]").Sheets("MrExcel")
    Set desWS = ThisWorkbook.Sheets("MrExcel")
    v1 = srcWS.Range("A7", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 27).Value
    v2 = desWS.Range("A7", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 5).Value
    For i = 1 To UBound(v2, 1)
        Val = v2(i, 1) & "|" & v2(i, 2)
        If Not rngList.Exists(Val) Then
            rngList.Add Key:=Val, Item:=i + 6
        End If
    Next i
    For i = 1 To UBound(v1, 1)
        Val = v1(i, 1) & "|" & v1(i, 2)
        If rngList.Exists(Val) Then
            If v1(i, 13) = "Y" Then
                desWS.Cells(rngList(Val), 3).Interior.ColorIndex = 6
            End If
            If v1(i, 20) = "Y" Then
                desWS.Cells(rngList(Val), 4).Interior.ColorIndex = 6
            End If
            If v1(i, 27) = "Y" Then
                desWS.Cells(rngList(Val), 5).Interior.ColorIndex = 6
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[excelnoob001]

Hi Mumps,

Your code works splendidly.

God bless you!

 

[mumps]

You are very welcome.