Highlighting Debits and Credits

Good Morning All,

I have a big project to do at work and I need to find all the debits and credits in the excel file for the particular account that they are in. I would do it manual but i have about 18000 line items which would take hours to go through. Is their a formula that can help me with this? They're are multiple duplicates in the file but i just want the debit and credits in the same account highlighted. The debit and credit numbers are in column H. I need to match the positive number with the negative number. Their can also be a duplicate of the same negative number which I dont need to worry about. As long as one positive and one negative number is highlighted. So in the end all the positve numbers that have duplicates should be highlighted. The debit and credit numbers also have to match the account name and account number which are in column G and F. Let me know if you need more information or if it's confusing. Sometimes my explanations can be a bit confusing. I have also attached a file of what it should look like.

Hi vharjani
I don't know of a formula but code can probably do as you require. Let me look at it.

hi vharjani,,,,,I think you are doing the reconicilation of accounts....Could you elaborate your requirement for further help....

Hi vharjani
Try this code...let me know of issues.

Please Login or Register  to view this content.

Hi vharjani
I've attached the file. The code should work on Excel 2007 and later (it's an .xlsm file)

okay so its working but now the code isn't stopping it just keeps going through the 18000 line items over and over again until i hit esc and then asks me to debug or to end...also going through my excel file it doesn't seem like it found all of the debits and credits it found a good amount but i still went through and found a lot more that weren't highlighted by the code. Do you know what the issue could be with this?

Okay so i figured out what the code doesn't capture. It doesn't capture numbers that have commas such as 27,122.10 as well as numbers that end in 0...such as 2712.10 or 2710.00. Is their anyway that can be fixed?

Hi vharjani

Attach a file that demonstrates the error. Please include the code in the file. I'll look at it.

Good Morning, I have attached a file with the code in which you can see that the numbers ending with zero and numbers that have commas are not going to be highlighted. Can i just fix this by changing the cell format? or do you have to do something to the code

Hi vharjani

Format the Cells in Column H as General. You can do it by code or manually and after the procedure runs, change the format back.
Here's the code to do it with code

Please Login or Register  to view this content.

Let me know of issues.

Hi vharjani

You're welcome...glad I could be of help. If that satisfies your need, I'd appreciate it if you'll please mark your thread as "Solved".

To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solved
- Click Save