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,504
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,504
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,081,441
Messages
5,358,694
Members
400,508
Latest member
fish31

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top