none
Matching the values in different sheets in a Excel RRS feed

  • Question

  • Hello All,

    My Excel file has numbers in column A in Sheet1 & column E in Sheet2.

    I need the numerical values in column A in sheet1 should get highligted with color red and gives a message "matches found" whenever I save the excel if the numerical values in column E in sheet 2 matches exactly. 

    However, the red highlight should be removed and no message requires, if I enter any data on the same row in column F in sheet1.

    Is there any macro for this

    Any help would be much appreciated

    Sunday, September 22, 2019 6:05 PM

Answers

  • Does this work as desired for both the cases?

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws1     As Worksheet
    Dim ws2     As Worksheet
    Dim lr      As Long
    Dim Rng     As Range
    Dim Cel     As Range
    Dim n       As Variant
    Dim m       As Variant
    Dim strE    As String
    Dim strF    As String
    Dim FoundE  As Boolean
    Dim FoundF  As Boolean
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    
    lr = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    Set Rng = ws1.Range("A2:A" & lr)
    Rng.Interior.ColorIndex = xlNone
    Rng.Font.ColorIndex = xlAutomatic
    
    strE = "Following Job# were found in Column E on Sheet2..." & vbNewLine & vbNewLine
    strF = "Following Job# were found in Column F on Sheet2..." & vbNewLine & vbNewLine
    
    For Each Cel In Rng
        n = Application.Match(Cel.Value, ws2.Columns(5), 0)
        m = Application.Match(Cel.Value, ws2.Columns(6), 0)
        
        If ws1.Cells(Cel.Row, "F") = "" Then
            If Not IsError(n) Then
                FoundE = True
                With Cel
                    .Interior.Color = vbRed
                    .Font.Color = vbWhite
                    strE = strE & Cel.Address(0, 0) & " (" & Cel.Value & ") was found in E" & n & " on Sheet2." & vbNewLine
                End With
            End If
            
            If Not IsError(m) Then
                FoundF = True
                With Cel
                    .Interior.Color = vbBlue
                    .Font.Color = vbWhite
                    strF = strF & Cel.Address(0, 0) & " (" & Cel.Value & ") was found in F" & m & " on Sheet2." & vbNewLine
                End With
            End If
        End If
    Next Cel
    
    If FoundE And FoundF Then
        Cancel = True
        MsgBox strE & vbNewLine & vbNewLine & strF, vbExclamation, "Job# Matching FoundE!"
    ElseIf FoundE Then
        Cancel = True
        MsgBox strE, vbExclamation, "Job# Matching FoundE!"
    ElseIf FoundF Then
        Cancel = True
        MsgBox strF, vbExclamation, "Job# Matching FoundE!"
    End If
    End Sub


    Subodh Tiwari (Neeraj) sktneer


    Friday, September 27, 2019 2:44 AM
  • To allow the user to Save the workbook, please delete all the instances of
    Cancel = True
    in the code.

    Subodh Tiwari (Neeraj) sktneer

    Tuesday, October 8, 2019 4:06 AM

All replies

  • What is your matching criteria?

    e.g.

    Is it Sheet1!A2=Sheet2!E2 and Sheet1!F2 is empty?

    OR

    Sheet1!A2 is found anywhere in column E on Sheet2 and Sheet1!F2 is empty?

    Why not upload a small sample file and mock up the desired output manually to show us what exactly you are trying to achieve?


    Subodh Tiwari (Neeraj) sktneer

    Thursday, September 26, 2019 4:22 AM
  • Hi Subodh,

    Greetings to you and thanks for the reply

    Below the link for the sample file

    jumpshare.com/v/gFm6Ga7xqu1rm560v5Ry   

    The Sheet1 has few job numbers in "Column A" in which 3 job numbers are matching with the job numbers in "Column E" in Sheet2.

    "Column A" in Sheet1  highlighted with 2 job numbers with red and the 1 is not highlighted because the cell in "Column F" has text in it.

    Happy to help if any further questions on this

    Thanks & regards


    John

    Thursday, September 26, 2019 5:32 AM
  • Hi John,

    Why do you want a macro for that?

    That can be achieved with the help of Conditional Formatting also.

    Please have a look at The File and let me know if that approach works for you.

    In the attached, I have inserted a Conditional Formatting rule in Column A on Sheet1 so that if a Job# is found in Column E on Sheet2 and if the corresponding cell in Column F on Sheet1 is empty, the cell with that Job# will be highlighted with Red background, White Bold & Italic Font and a Border around. You may set the desired format for the existing Conditional Formatting rule as per your choice.


    Subodh Tiwari (Neeraj) sktneer

    Thursday, September 26, 2019 6:36 AM
  • Hi Subodh,

    Its because I need an alert message whenever the user saved the shared Excel file.

    Thursday, September 26, 2019 6:52 AM
  • Also kindly share the screenshot for the work around on setting conditional formatting rule to get the desired format when time allows
    Thursday, September 26, 2019 6:56 AM
  • What do you want macro to do then other than displaying a message if it meets the criteria? Would you like to prevent the file from Saving allowing user to perform some actions?

    Subodh Tiwari (Neeraj) sktneer

    Thursday, September 26, 2019 6:58 AM
  • I think the conditional format rule would work fine, could you plz share the work around to set the format
    Thursday, September 26, 2019 7:28 AM
  • Here are some screenshots which you may refer to set the conditional formatting rule to column A on Sheet1.

    Let me know if you still have any issue in applying the conditional formatting.


    Subodh Tiwari (Neeraj) sktneer

    Thursday, September 26, 2019 7:56 AM
  • Hi Subodh,

    It works good and thanks a lot for spending your time on created and posted the screenshots.

    The issue would be it stopped working after some time as we are using the shared excel in multiple system.

    It would be greate, If I get a macro to alert and highlight if the job number matches from Column A in sheet1 to Column E in Sheet2 only when we save the work sheet, Is that possible?

    Thanks & regards

    John 

    Thursday, September 26, 2019 5:58 PM
  • Place the following code on ThisWorkbook Module.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws1     As Worksheet
    Dim ws2     As Worksheet
    Dim lr      As Long
    Dim Rng     As Range
    Dim Cel     As Range
    Dim n       As Variant
    Dim str     As String
    Dim Found   As Boolean
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    
    lr = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    Set Rng = ws1.Range("A2:A" & lr)
    Rng.Interior.ColorIndex = xlNone
    Rng.Font.ColorIndex = xlAutomatic
    
    str = "Following Job# were found on Sheet2..." & vbNewLine & vbNewLine
    
    For Each Cel In Rng
        n = Application.Match(Cel.Value, ws2.Columns(5), 0)
        If Not IsError(n) Then
            Found = True
            ws1.Select
            With Cel
                .Select
                .Interior.Color = vbRed
                .Font.Color = vbWhite
                str = str & Cel.Address(0, 0) & " (" & Cel.Value & ") was found in E" & n & " on Sheet2." & vbNewLine
            End With
        End If
    Next Cel
    
    If Found Then
        Cancel = True
        MsgBox str, vbExclamation, "Job# Matching Found!"
    End If
    End Sub


    Subodh Tiwari (Neeraj) sktneer

    Thursday, September 26, 2019 6:42 PM
  • Hi Subodh,

    Yes, it gives the desired results and one more final change requires

    It also alert if the job number in Column A: in sheet1 matching the job number in "Column F" too with some differnet colors other than red

    The highlights should be removed in both cases and no message requires, if I enter any data on the same row in column F in sheet1 as requested earlier.

    https://jumpshare.com/v/y4A4tWekZb2YnLbTYxzE#

    Friday, September 27, 2019 1:14 AM
  • Please try this tweaked code.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws1     As Worksheet
    Dim ws2     As Worksheet
    Dim lr      As Long
    Dim Rng     As Range
    Dim Cel     As Range
    Dim n       As Variant
    Dim str     As String
    Dim Found   As Boolean
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    
    lr = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    Set Rng = ws1.Range("A2:A" & lr)
    Rng.Interior.ColorIndex = xlNone
    Rng.Font.ColorIndex = xlAutomatic
    
    str = "Following Job# were found on Sheet2..." & vbNewLine & vbNewLine
    
    For Each Cel In Rng
        n = Application.Match(Cel.Value, ws2.Columns(5), 0)
        If Not IsError(n) And ws1.Cells(Cel.Row, "F") = "" Then
            Found = True
            ws1.Select
            With Cel
                .Select
                .Interior.Color = vbRed
                .Font.Color = vbWhite
                str = str & Cel.Address(0, 0) & " (" & Cel.Value & ") was found in E" & n & " on Sheet2." & vbNewLine
            End With
        End If
    Next Cel
    
    If Found Then
        Cancel = True
        MsgBox str, vbExclamation, "Job# Matching Found!"
    End If
    End Sub


    Subodh Tiwari (Neeraj) sktneer

    Friday, September 27, 2019 1:20 AM
  • Is that possible to give an alert if the job number in Column A: in sheet1 matching the job number in "Column F" as well with some differnet colors other than red.

    If you thnink, it is too long thread, we will close it here and  I would like to say a big big thanks to you for the follow up and provide the desired results

    Thank you

    John

    Friday, September 27, 2019 2:24 AM
  • Does this work as desired for both the cases?

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws1     As Worksheet
    Dim ws2     As Worksheet
    Dim lr      As Long
    Dim Rng     As Range
    Dim Cel     As Range
    Dim n       As Variant
    Dim m       As Variant
    Dim strE    As String
    Dim strF    As String
    Dim FoundE  As Boolean
    Dim FoundF  As Boolean
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    
    lr = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    Set Rng = ws1.Range("A2:A" & lr)
    Rng.Interior.ColorIndex = xlNone
    Rng.Font.ColorIndex = xlAutomatic
    
    strE = "Following Job# were found in Column E on Sheet2..." & vbNewLine & vbNewLine
    strF = "Following Job# were found in Column F on Sheet2..." & vbNewLine & vbNewLine
    
    For Each Cel In Rng
        n = Application.Match(Cel.Value, ws2.Columns(5), 0)
        m = Application.Match(Cel.Value, ws2.Columns(6), 0)
        
        If ws1.Cells(Cel.Row, "F") = "" Then
            If Not IsError(n) Then
                FoundE = True
                With Cel
                    .Interior.Color = vbRed
                    .Font.Color = vbWhite
                    strE = strE & Cel.Address(0, 0) & " (" & Cel.Value & ") was found in E" & n & " on Sheet2." & vbNewLine
                End With
            End If
            
            If Not IsError(m) Then
                FoundF = True
                With Cel
                    .Interior.Color = vbBlue
                    .Font.Color = vbWhite
                    strF = strF & Cel.Address(0, 0) & " (" & Cel.Value & ") was found in F" & m & " on Sheet2." & vbNewLine
                End With
            End If
        End If
    Next Cel
    
    If FoundE And FoundF Then
        Cancel = True
        MsgBox strE & vbNewLine & vbNewLine & strF, vbExclamation, "Job# Matching FoundE!"
    ElseIf FoundE Then
        Cancel = True
        MsgBox strE, vbExclamation, "Job# Matching FoundE!"
    ElseIf FoundF Then
        Cancel = True
        MsgBox strF, vbExclamation, "Job# Matching FoundE!"
    End If
    End Sub


    Subodh Tiwari (Neeraj) sktneer


    Friday, September 27, 2019 2:44 AM
  • Thank you so much Subodh for helped me twice. You Rock!!!

    Friday, September 27, 2019 2:54 AM
  • You're welcome John! Glad I could help.

    Thanks for the feedback!


    Subodh Tiwari (Neeraj) sktneer

    Friday, September 27, 2019 3:16 AM
  • To allow the user to Save the workbook, please delete all the instances of
    Cancel = True
    in the code.

    Subodh Tiwari (Neeraj) sktneer

    Tuesday, October 8, 2019 4:06 AM
  • Hi Subodh,

    The macro is all good, however I unable to save the work book.

    It throws the message but the work books is not saved.

    So, is there any way the macro save the work book first and then pop up the message

    Tuesday, October 8, 2019 4:13 AM
  • Thanks again Subodh
    Tuesday, October 8, 2019 4:18 AM
  • You're welcome!

    Subodh Tiwari (Neeraj) sktneer

    Tuesday, October 8, 2019 4:19 AM
  • Hi Subodh,

    I am not sure how to use this macro through Excel Add-In.

    I have an Add-In template, however unable to attach this macro with my Add-in in shared file.

    It would be great If I get any Add-in to attach and work through.

    Thanks & regards

    John


    Thursday, October 24, 2019 8:25 PM