Previous Topic Next Topic

Script which can check if a debit has a corresponding credit

Solved/Closed
dennisvhansen Posts 7 Registration date Wednesday September 9, 2015 Status Member Last seen September 24, 2015 - Sep 9, 2015 at 07:44 AM
dennisvhansen Posts 7 Registration date Wednesday September 9, 2015 Status Member Last seen September 24, 2015 - Sep 24, 2015 at 02:30 AM
Hello,

I am new to VBA, and I am searching for a script which can check if a debit has a corresponding credit (same value bue with -/+), and highlight the cells in green. Cells that doesn't have a corresponding cell needs to be highlighted in red. I want to check from cell B2 to L56, as there are several subsidiarys I need to match.

I have been looking everywhere for this, but with no luck so far.

I hope you are able to help.

Regards,

Dennis
Related:

3 responses

Answer 1 / 3
RayH
Sep 16, 2015 at 08:35 PM
Using the file you posted.
I skipped the zero values, as these would all match each other.
You can change the ranges to suite. 


Sub finddupes()
dim found as Boolean

For Each mycell In Worksheets("Ark1").Range("B2:L34")
myvalue = Abs(mycell.Value)
If myvalue <> 0 Then
found = False
    For Each chkcell In Worksheets("Ark1").Range("B2:L34")
        If mycell <> chkcell Then
            If myvalue = Abs(chkcell.Value) Then
                chkcell.Interior.ColorIndex = 4 ' Green
                mycell.Interior.ColorIndex = 4
                found = True
            End If
        End If
    Next
If found = False Then mycell.Interior.ColorIndex = 3 'Red
End If
Next

End Sub
1
dennisvhansen Posts 7 Registration date Wednesday September 9, 2015 Status Member Last seen September 24, 2015
Sep 17, 2015 at 03:14 AM
This is just perfect! Exactly what I have been looking for, you have saved me for several hours of work :) Highly appreciated!
0
dennisvhansen Posts 7 Registration date Wednesday September 9, 2015 Status Member Last seen September 24, 2015
Sep 17, 2015 at 03:16 AM
A comment though... Would it be possible to add another color, say yellow... For Numbers that are within, say 100, of each other? So I know that it is only a minor difference?
0

Similar discussions

formula if you have credit in one column, debit in next column
Jack -
Jackthelad -

4 responses
Code to move rows into another sheet based on certain values
nathaliecannell -
vcoolio -

1 response
Automatically Run Macro from a calculated cell value change
smiller -
TrowaD -

1 response
I need a macro that can extract data
excelmwangi -
vcoolio -

26 responses
macro for inserting multiple rows between data
vba dummy -
TrowaD -

1 response
Copy rows to other sheets based on value in column
LeedMe -
vcoolio -

6 responses