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