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

excelnoob001

New Member
Joined
Jul 24, 2019
Messages
26
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

Rich (BB code):
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 :nya:.
Any help or insight is greatly appreciated. :)
Thanks!
 
Last edited:
Try:
Code:
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
 
Last edited:
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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. :rofl:

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.
 
Upvote 0
Try this version in the Master. Change the source workbook name (in red) to suit your needs.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top