try such code in standard module (Alt+F11 Insert Module):
Sub sums_compare()
Dim sumupload As Currency, summaster As Currency, currentcolumn As Long
With Application.WorksheetFunction
sumupload = .Sum(Sheets("Upload").Columns("P:P"))
currentcolumn = .Match(Sheets("Ctr").Range("C6"), Sheets("Master").Rows("6:6"), 0)
summaster = .Sum(Sheets("Master").Cells(47, currentcolumn).Resize(13, 1))
If summaster - sumupload = 0 Then
MsgBox "Agreed for " & Format(Sheets("Ctr").Range("C6"), "mmm yy")
Else
MsgBox "For " & Format(Sheets("Ctr").Range("C6"), "mmm yy") & " difference is: " & summaster - sumupload
End If
End With
End Sub
As you can see these are basically excel formulas just called from within the code.
if you want it to pop-up automatically after changing C6 in Ctr then in sheet code (right click on the sheet name tab and edit code) insert:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("C6"), Target) Is Nothing Then
Call sums_compare
End If
End Sub
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks