Compare 2 sheets in workbook and display in pop up msg

[IKHAN]

Hi, Having trouble with this issue below, Any help would be really appreciated.


when i copy data or enter data into sheet 1 column A, It should check with the data in sheet 5 column A and if there is a match of 2 exact same item but ending with B ,It should pop up msg in sheet 1 - displaying "Match found for below" and display the matched contents.


Note : It should display only if both items match (example apple and appleb)and shouldn't display if only Apple is present in column a of sheet 1


EXAMPLE


SHEET 1(COL A )-- SHEET 5(COL A)


APPLE --- APPLE

APPLEB ---- APPLEB

ORANGE --- ORANGE

ORANGEB --- GRAPES

LEMON ---

cASHEW ---


RESULT REQD IN SHEET 1


POP UP MSG


"Alert !! following has matched"


APPLE

APPLEB

 

[Luke M]

Right click on sheet tab, view code, paste this in. Note that this will be displayed as data is entered.

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Dim MyValue As String
Dim MyBValue As String

If UCase(Right(Target, 1)) = "B" Then
MyValue = Left(Target.Value, Len(Target) - 1)
MyBValue = Target.Value
Else
MyValue = Target.Value
MyBValue = Target.Value & "B"
End If

If WorksheetFunction.CountIf(Me.Range("A:A"), MyValue) > 0 And _
WorksheetFunction.CountIf(Me.Range("A:A"), MyBValue) > 0 And _
WorksheetFunction.CountIf(Worksheets("Sheet 5").Range("A:A"), MyValue) > 0 And _
WorksheetFunction.CountIf(Worksheets("Sheet 5").Range("A:A"), MyBValue) > 0 Then

MsgBox "Alert!! Following has matched" & vbNewLine & MyValue & vbNewLine & MyBValue, vbOKOnly, "Matches found"
End If
End Sub

[/pre]

 

[IKHAN]

Thanks for saving my day Luke.


Worked just fine except if in sheet 1 column A(R1:apple and R2:appleb) are copied msg doesn't pop up,However if i copy in sheet 1 COLUMN A - R1:apple then R2range and then r3:appleb ?(msg pops up)


SHEET 1(COL A )-- SHEET 5(COL A) ---- it doesn't work


R1:APPLE --- APPLE

R2:APPLEB ---- APPLEB

R3:ORANGE --- ORANGE


SHEET 1(COL A )-- SHEET 5(COL A) ---- IT WORKS


R1:APPLE --- APPLE

R2:ORANGE --- ORANGE

R3:APPLEB ---- APPLEB

 

[Luke M]

Correct, macro was built to only trigger when one cell is changed. Handling multiple cells gets tricky. That said, this macro might not be as robust, but it *should* work.

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

Dim MyValue As String
Dim MyBValue As String

For Each c In Target
If UCase(Right(c, 1)) = "B" Then
MyValue = Left(c.Value, Len(c) - 1)
MyBValue = c.Value
Else
MyValue = c.Value
MyBValue = c.Value & "B"
End If

If WorksheetFunction.CountIf(Me.Range("A:A"), MyValue) > 0 And _
WorksheetFunction.CountIf(Me.Range("A:A"), MyBValue) > 0 And _
WorksheetFunction.CountIf(Worksheets("Sheet 5").Range("A:A"), MyValue) > 0 And _
WorksheetFunction.CountIf(Worksheets("Sheet 5").Range("A:A"), MyBValue) > 0 Then

MsgBox "Alert!! Following has matched" & vbNewLine & MyValue & vbNewLine & MyBValue, vbOKOnly, "Matches found"
End If
Next c
End Sub

[/pre]

 

[IKHAN]

IT WORKED!!! THANKS A LOT

 

[Luke M]

Awesome, glad it worked. =)