Check 2 cells from source to master file, if match, highligh cell X in master
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

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

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

    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

    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 .
    Any help or insight is greatly appreciated.
    Thanks!
    Last edited by excelnoob001; Aug 12th, 2019 at 02:04 PM.

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

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

    You mentioned column M. What are the other 2 error-checking columns in the source file?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    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

    The other two are "T" and "AA".

  4. #4
    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

    Ah sorry, I put the wrong comment in my code. It should say 'checks M7 to last row', 'checks T7 to last row', and checks 'AA7 to last row'.

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

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

    Place this macro in the source file. Change the workbook name (in red) to suit your needs.
    Code:
    Sub HighlightCell()
        Application.ScreenUpdating = False
        Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, fnd As Range, x As Long, sAddr As String
        Set srcWS = ThisWorkbook.Sheets("MrExcel")
        Set desWS = Workbooks("Master.xlsx").Sheets("MrExcel")
        LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For x = 13 To 27 Step 7
            Set fnd = Range(Cells(7, x), Cells(LastRow, x)).Find("Y", LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                sAddr = fnd.Address
                Do
                    If Range("A" & fnd.Row) = desWS.Range("A" & fnd.Row) And Range("B" & fnd.Row) = desWS.Range("B" & fnd.Row) Then
                        desWS.Range("A" & fnd.Row).Interior.ColorIndex = 6
                    End If
                    Set fnd = Range(Cells(7, x), Cells(LastRow, x)).FindNext(fnd)
                Loop While sAddr <> fnd.Address
                sAddr = ""
            End If
        Next x
        Application.ScreenUpdating = True
    End Sub
    Last edited by mumps; Aug 12th, 2019 at 02:46 PM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  6. #6
    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

    If possible, can you comment each line of code and explain what it is doing?

    Thank you so much!

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

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

    I hope this helps.
    Code:
    Sub HighlightCell()
        Application.ScreenUpdating = False 'turns off screen refreshing, preventing screen flicker and speeds up the macro
        Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, fnd As Range, x As Long, sAddr As String
        Set srcWS = ThisWorkbook.Sheets("MrExcel") 'sets the source sheet
        Set desWS = Workbooks("Master").Sheets("MrExcel") 'sets the destination sheet
        LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'finds last used row in the source sheet
        For x = 13 To 27 Step 7 'loos through every 7th column starting in column M
            Set fnd = Range(Cells(7, x), Cells(LastRow, x)).Find("Y", LookIn:=xlValues, lookat:=xlWhole) 'searches for "Y" in each column
            If Not fnd Is Nothing Then 'if "Y" is found, executes next line of code
                sAddr = fnd.Address 'find the address of the found "Y"
                Do 'starts a loop to search fro all Y's in the column
                    If Range("A" & fnd.Row) = desWS.Range("A" & fnd.Row) And Range("B" & fnd.Row) = desWS.Range("B" & fnd.Row) Then 'compares columns A and B of one sheet to the other sheet
                        desWS.Range("A" & fnd.Row).Interior.ColorIndex = 6 'if there is a match, colors cell in column A
                    End If
                    Set fnd = Range(Cells(7, x), Cells(LastRow, x)).FindNext(fnd) 'looks for next occurrence of "Y" in the column
                Loop While sAddr <> fnd.Address 'loops through the column until it reaches the first found "Y" and then exits the loop
                sAddr = ""
            End If
        Next x 'goes to next column
        Application.ScreenUpdating = True 'turns screen refreshing back on
    End Sub
    Last edited by mumps; Aug 12th, 2019 at 03:13 PM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  8. #8
    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 did a test run of your code by having M7 and down contain ALL Y. On the master file, it didn't highlight ALL of column A7 and down.

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

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

    I tested the macro on some dummy data and it worked properly. The macro looks for an uppercase "Y" so make sure that is the case with your data. Also make sure there are no leading or trailing spaces to the left and right of the "Y". If you are still having problems, perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  10. #10
    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

    So sorry for not pointing this out earlier, but the column A and B of my master file, worksheet "MrExcel" isn't in the same order as the column A and B of my source file, worksheet "MrExcel". For the rows of column A and B that did match, that specific row in column A of the master file did highlight.

    For example, source file, worksheet "MrExcel" could go like this:

    Column A Column B
    A Hi
    B Bye
    C Love


    Master File, worksheet "MrExcel"
    Column A Column B
    C Love
    B Bye
    A Hi


    For this purpose, let's say Y was found for all those three rows.
    Only the second row would be highlighted.
    However, it wouldn't highlight rows 1 or 3.

    Thank you kind sir, hope that clears everything up.
    Last edited by excelnoob001; Aug 13th, 2019 at 08:19 AM.

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
  •