Dim payment As Double
Dim deposit As Double
Dim intPayment As Double
Dim intDeposit As Double
Dim endOfIntRegister As Integer
Dim endOfProdRegister As Integer
Dim endOfBankStatement As Integer
Dim intRegisterStart As Integer
Dim prodRegisterStart As Integer
Dim bankStart As Integer
Dim timesPaymentFound As Integer
Dim timesDepositFound As Integer


'times found
timesPaymentFound = 0
timesDepositFound = 0

'First rows
intRegisterStart = 3
prodRegisterStart = 3
bankStart = 3

'Last rows
endOfIntRegister = 77
endOfProdRegister = 18
endOfBankStatement = 149


'Check payments of int register
For i = bankStart To endOfBankStatement
    payment = Cells(i, 27).Value
    If payment > 0 Then
    For j = intRegisterStart To endOfIntRegister
        intPayment = Cells(j, 9).Value
        If payment = intPayment And IsEmpty(Cells(j, 11)) Then
            Cells(j, 11).Value = Cells(i, 25).Value
            Cells(j, 12).Value = payment
            timesPaymentFound = timesPaymentFound + 1
            Exit For
        End If
    Next j
    End If
    'if not found in register highlight paymment
    If payment > 0 And timesPaymentFound <= 0 Then
        For k = 24 To 27
            Cells(i, k).Interior.Color = RGB(255, 255, 153)
            timesPaymentFound = 0
        Next k
    End If
    'if found, grey it out
    If payment > 0 And timesPaymentFound > 0 Then
        For k = 24 To 27
        Cells(i, k).Interior.Color = RGB(230, 230, 230)
        Next k
    End If
    timesPaymentFound = 0
Next i


'Check Deposits of int register
For i = bankStart To endOfBankStatement
    deposit = Cells(i, 26).Value
    If deposit > 0 Then
    For j = intRegisterStart To endOfIntRegister
        intDeposit = Cells(j, 7).Value
        If deposit = intDeposit And IsEmpty(Cells(j, 11)) Then
            Cells(j, 11).Value = Cells(i, 25).Value
            Cells(j, 10).Value = deposit
            timesDepositFound = timesDepositFound + 1
            Exit For
        End If
    Next j
    'if not found in register, highlight bank statement line in yellow
        If deposit > 0 And timesDepositFound <= 0 Then
            For k = 24 To 27
                Cells(i, k).Interior.Color = RGB(181, 91, 31)
            Next k
        End If
    'if found in register, grey out of bank statement
        If deposit > 0 And timesDepositFound > 0 Then
            For k = 24 To 27
                Cells(i, k).Interior.Color = RGB(230, 230, 230)
            Next k
        End If
    End If
    'reset times found
    timesDepositFound = 0
Next i

'highlight unreconciled
For i = 3 To 92
    'if description is empty, bold and highlight that row
   If IsEmpty(Cells(i, 11)) Then
        For j = 1 To 9
            Cells(i, j).Interior.Color = RGB(255, 255, 153)
            Cells(i, j).Font.Bold = True
        Next j
    Else
    'if description has something, fade that transaction out
       For j = 1 To 9
            Cells(i, j).Interior.Color = RGB(220, 220, 220)
            Cells(i, j).Font.Color = RGB(100, 100, 100)
        Next j
    End If
Next i