Selected Answer
Hello Omaran,
If I understand correctly what you are trying to do, using your uploaded file for reference, this is what I get.
Compare "D7" value to "B9" value; compare "D17" value to "B19" value; and "D25" value to "B27" value. To do this I have modified your code in the following ways:
Added a check at the start to make sure only one cell is selected
Gave (Set) a value to rngA and removed the "-2" for the lr value.
Declared variable "lr" and gave it a value.
Modified the "If" statement to compare the value entered in column "D" opposite "DISCOUNT" to the value below "NET TOTAL" in column "B"
The revised macro is:
Private Sub Worksheet_Change(ByVal Target As Range)
' check if multple cells are selected
If Target.Count > 1 Then Exit Sub
Dim rngA As Range
Dim cell As Range
Dim lr As Long
lr = Cells(Rows.Count, 2).End(xlUp).Row
Set rngA = Sheets("Jan BY").Range("B1:B" & lr)
' check if selected cell is in column "D"
If ActiveCell.Column <> 4 Then Exit Sub
If Not Intersect(ActiveCell, Range("D1:D" & lr)) Is Nothing Then
For Each cell In rngA
If cell.Value = "NET TOTAL" Then
' compare value in column "D" to the value below "NET TOTAL"
If cell.Offset(-1, 2).Value > cell.Offset(1).Value Then
MSGBOX ("EXCEEDS NUMBER !"), vbCritical
Target.Value = "" ' delete the value in column "D"
End If
End If
Next
End If
End Sub
'
If this is what you are trying to do please mark my answer as Selected.
Cheers :-)
