Condition should applied if columns total value is not match

[shamim]

Hi,

In attached excel file sheet there are two columns dedicated for net and vat amount and ultimately the gross amount is comprises by totaling the Net+Vat amount. That means

Net + Vat = Gross amount. If the Gross amount is not matching with sum of Net + Vat then cell interior color should be red.

For better outlook I already change interior color of cell D4 and D5. The total gross amount is not matching with Net+Vat amount.

I want to achieve this process through Macro.

Regards,
Shamim

[Bob Phillips]

Public Sub FlagNonMatching()
Dim lastrow As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("D2").Resize(lastrow - 1)

.FormatConditions.Add Type:=xlExpression, Formula1:="=D2<>B2+c2"
.FormatConditions(1).Interior.Color = vbRed
End With
End With
End Sub

[shamim]

Thanks for the prompt response. Can this be done with Msgbox prompt " Different found in Column D"?

Regards,
Shamim

[Bob Phillips]

Public Sub FlagNonMatching()
Const FORMULA_CHECK As String = "SUMPRODUCT(--(B2:B<lastrow>+C2:C<lastrow>=D2:D<lastrow>))=COUNT(D2:D<lastrow>)"
Dim lastrow As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("D2").Resize(lastrow - 1)

.FormatConditions.Add Type:=xlExpression, Formula1:="=D2<>B2+c2"
.FormatConditions(1).Interior.Color = vbRed
End With

If Not .Evaluate(Replace(FORMULA_CHECK, "<lastrow>", lastrow)) Then

MsgBox "Differences detected", vbOKOnly, "Check Am,ounts"
End If
End With
End Sub