Script which can check if a debit has a corresponding credit

Solved/Closed

Excel

dennisvhansen Posts 7 Registration date Wednesday 9 September 2015 Status Member Last seen 24 September 2015 - 9 Sep 2015 à 07:44
dennisvhansen Posts 7 Registration date Wednesday 9 September 2015 Status Member Last seen 24 September 2015 - 24 Sep 2015 à 02:30

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

Debit summation meaning
Credit summation meaning - Guide
Vostfr meaning - Guide
Leechers meaning - Guide
Orange heart meaning - Guide
Bomboclaat meaning - Guide

3 responses

Réponse 1 / 3

RayH
16 Sep 2015 à 20:35

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

dennisvhansen Posts 7 Registration date Wednesday 9 September 2015 Status Member Last seen 24 September 2015
17 Sep 2015 à 03:14

This is just perfect! Exactly what I have been looking for, you have saved me for several hours of work :) Highly appreciated!

dennisvhansen Posts 7 Registration date Wednesday 9 September 2015 Status Member Last seen 24 September 2015
17 Sep 2015 à 03:16

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?

Réponse 2 / 3

venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
10 Sep 2015 à 04:16

please post a very small extract of your sheet

dennisvhansen Posts 7 Registration date Wednesday 9 September 2015 Status Member Last seen 24 September 2015
10 Sep 2015 à 06:30

http://postimg.org/image/40hbtw8eb/

Here is the small extract of what you requested... I have started doing some of it manually, and as you can see I have put 2 rings around 2 cells that have an exact match (debit/credit)... The ones that are red, are ones where it doesn't have a match. As the numbers are large, I don't think it will ever occur that more than 2 cells have the same value.

I hope this makes sense.

Réponse 3 / 3

venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
11 Sep 2015 à 01:35

this is image. I cannot transfer data to my excel file. besides there should be column headings. It looks any data in any column may correspond with data in any other column, are you familiar with macro?

suggest save a sample file and send it(as an attchment) through "speedyshare.com"

dennisvhansen Posts 7 Registration date Wednesday 9 September 2015 Status Member Last seen 24 September 2015
16 Sep 2015 à 02:45

Sorry for the late reply, I have not been in the office since thursday.

I've uploaded it now.
There are column headings in the uploaded document.

https://authentification.site/P8SWD/Small-extract.xlsx

I am afraid, I am not too familiar with macros.

Similar discussions

formula if you have credit in one column, debit in next column

Code to move rows into another sheet based on certain values

Automatically Run Macro from a calculated cell value change

I need a macro that can extract data

macro for inserting multiple rows between data

Copy rows to other sheets based on value in column

Similar discussions

Subscribe To Our Newsletter!