Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

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

  1. #11
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,251
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

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

    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 by mumps; Aug 13th, 2019 at 08:58 AM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  2. #12
    New Member
    Join Date
    Jul 2019
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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/itbcdcaqty...urce.xlsx?dl=0 (source file)

    https://www.dropbox.com/s/oej1zh49c6...ster.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.

  3. #13
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,251
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

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

    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("Mr Excel Source.xlsx").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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #14
    New Member
    Join Date
    Jul 2019
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi Mumps,

    Your code works splendidly.

    God bless you!

  5. #15
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,251
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

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

    You are very welcome.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •