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

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,600
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:

excelnoob001

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

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

Well-known Member
Joined
Apr 11, 2012
Messages
8,600
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
 

Forum statistics

Threads
1,085,063
Messages
5,381,506
Members
401,744
Latest member
Schoen

Some videos you may like

This Week's Hot Topics

Top