Validate Date and Compare Amounts

[Pratap 1987]

Hi there,

Could one of you please help me in building a macro for the below requirement.
Basically, I am trying to compare the Dates and Amounts in 2 different columns and highlight exceptions:

I have a spreadsheet - Where Col K & V have Dates on it - And Col N & Z has amounts on it.
Need a macro to look for the Dates from Col K to V and if match found - Compare the amounts from Col N to Z.
Any mismatch in amounts greater than 0.99 or any amounts which does not have a match should be highlighted in Red.

Points to note:
Amounts in Col N & Z - Could be in + or - sign - So the amounts are compared - It should look for opposite signs.
Mismatch should be highlighted in both ways - ie, Mismatches in Col N and Col Z has to be highlighted (not just 1 Col).

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

1

Date

Db (They)

Date

Cr (We)

2

1-Jul-16

-100.00

2-Jul-16

3

1-Jul-16

2-Jul-16

4

2-Jul-16

5-Jul-16

700.00

5

2-Jul-16

6-Jul-16

6

4-Jul-16

-350.00

4-Jul-16

7

4-Jul-16

4-Jul-16

350.00

8

5-Jul-16

-700.00

1-Jul-16

100.00

9

6-Jul-16

-111.00

1-Jul-16

[mikerickson]

I note that there are duplicate dates in each of those columns.

How do you want that handled? Are we looking at the sum of the values for a particular date or at each of the entries for that date?

e.g. what if Z9 contained 30 (as well as Z8 containing 100)

[SamT]

2 @ 2d arrays
First dimension = Row number
Second dimension debit array = Dateserial + (dollar value /10^5) * -1
Second Dimension credit array = Dateserial + Dollar value/10^5

Sort both arrays by second dimension

For i to Ubound
If not cr(i,2) = db(i,2) then error, check if +- 99/10^7
If not then mark Cells both arrays(i,1)

[Pratap 1987]

Hi Sam, I'm not too familiar with VBAs. Would you be able to build one for me? Please

[Pratap 1987]

Hi Mike - They should be handled at each of the entries for that date.

[Paul_Hossler]

Brute force, and not extremely efficient, but I tried to make it easily maintainable

Option Explicit

Sub Compare()
    Dim rDate1 As Range, rAmt1 As Range, rDate2 As Range, rAmt2 As Range
    Dim iLastRow1 As Long, i As Long, iLastRow2 As Long, j As Long
    
    'init
    Set rDate1 = ActiveSheet.Range("K:K")
    Set rAmt1 = ActiveSheet.Range("N:N")
    Set rDate2 = ActiveSheet.Range("V:V")
    Set rAmt2 = ActiveSheet.Range("Z:Z")
    'clear color
    rDate1.Interior.ColorIndex = xlNone
    rAmt1.Interior.ColorIndex = xlNone
    rDate2.Interior.ColorIndex = xlNone
    rAmt2.Interior.ColorIndex = xlNone
    
    'get last rows
    iLastRow1 = rDate1.Cells(1, 1).End(xlDown).Row
    iLastRow2 = rDate2.Cells(1, 1).End(xlDown).Row
    'compare data and abs amount
    For i = 2 To iLastRow1
        For j = 2 To iLastRow2
            If (rDate1(i).Value = rDate2(j).Value) And (Abs(rAmt1(i).Value) = Abs(rAmt2(j).Value)) Then
                rDate1(i).Interior.Color = vbGreen
                rAmt1(i).Interior.Color = vbGreen
                rDate2(j).Interior.Color = vbGreen
                rAmt2(j).Interior.Color = vbGreen
            End If
        Next j
    Next i
    'fill 1 not matched
    For i = 2 To iLastRow1
        If rDate1(i).Interior.ColorIndex = xlNone Then
            rDate1(i).Interior.Color = vbRed
            rAmt1(i).Interior.Color = vbRed
        End If
    Next i
    'fill 2 not matched
    For j = 2 To iLastRow2
        If rDate2(j).Interior.ColorIndex = xlNone Then
            rDate2(j).Interior.Color = vbRed
            rAmt2(j).Interior.Color = vbRed
        End If
    Next j
End Sub

[mikerickson]

Hi Mike - They should be handled at each of the entries for that date.

What does this mean?
Could you attach a sample workbook (with a few duplicate entries) and an example of the desired result?