if VBA

[jtodd]

Hi
How do check if H2 = B2 and have a message if not , I have this so far.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "H2" Then
If Target.Value <> "B2" Then
MsgBox "Wrong Sku!"
End If
End If
End Sub

Would like it to work on all cells in H and B

 

[nemmi69]

try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Or Target.Column = 8 Then
    If Cells(Target.Row, 2).Value = Cells(Target.Row, 8).Value Then
        MsgBox Cells(Target.Row, 2).Address & " MATCHES " & Cells(Target.Row, 8).Address
    Else
        MsgBox Cells(Target.Row, 2).Address & " DOES NOT MATCH " & Cells(Target.Row, 8).Address
    End If
End If
End Sub

 

[Yongle]

Or this, which also clears the value of the Target cell if SKU is not correct

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 Or Target.Column <> 8 Then Exit Sub

    If Target.Value <> Target.Offset(, -6).Value Then
        MsgBox Target.Value & " is incorrect Sku!"
            With Target
                 Application.EnableEvents = False
                .ClearContents
                .Select
                Application.EnableEvents = True
            End With
    End If

End Sub

 

[jtodd]

First thanks for quick reply.

The code work fine until i use a vlookup for H2 ,
H2 looks up G2 , when I enter data in G2 - H2 changes but the code does not run?

 

[jtodd]

Hi
Why does this not work when the target cell is changed using Vlookup?

 

[Yongle]

You are using an Event macro which is triggerred when the cell is EDITED
Value returned by formula does not trigger an event macro when the value returned changes

 

[jtodd]

Thanks that explains it .
Is there way of making this work when trigered by a formula?

 

[Yongle]

Not directly

- what is triggering the value returned by VLOOKUP to change?
- are you manually editing a value elsewhere?

 

[jtodd]

Cell G2 is the cell that is changed , its populated by scanning data into it

 

[Yongle]

I do not know if the macro will be triggered or not
- give it a go and let me know

The macro is triggered when a value in column G is edited
- the value in column B is then compared with the value in column H

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 Or Target.Column <> 7 Then Exit Sub

    If Target.Offset(, 1).Value <> Target.Offset(, -5).Value Then
        MsgBox Target.Value & " is incorrect Sku!"
        Target.Select
    End If
End Sub