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 :p.
Any help or insight is greatly appreciated. :)
Thanks!
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
You mentioned column M. What are the other 2 error-checking columns in the source file?
 
Upvote 0
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'.
 
Upvote 0
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("[COLOR="#FF0000"]Master.xlsx[/COLOR]").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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 AColumn B
AHi
BBye
CLove


<tbody>
</tbody>


Master File, worksheet "MrExcel"
Column AColumn B
CLove
BBye
AHi

<tbody>
</tbody>


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:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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