Script which can check if a debit has a corresponding credit

Solved/Closed

Excel

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

Macro for matching debits and credits in excel
Excel date format dd.mm.yyyy - Guide
Spell number in excel without macro - Guide
Screenshot in excel - Guide
1st, 2nd, 3rd position formula in excel ✓ - Office Software Forum
Macros in excel download - Download - Spreadsheets

3 replies

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

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!

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?

Answer 2 / 3

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 10, 2015 at 04:16 AM

please post a very small extract of your sheet

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

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.

Answer 3 / 3

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 11, 2015 at 01:35 AM

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 September 9, 2015 Status Member Last seen September 24, 2015
Sep 16, 2015 at 02:45 AM

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!