Macro to loop through a list of numbers, and hightlight number depending on criteria

[shredda23]

Hi guys, I am an financial analyst and I have a have to review a 50 to 100 trial balances each month. Our system has around 200 accounts in a trial balance. See short example below, the account number is in column A and the amount is in the same row in column C.

Example: a b c
10110 $400
12100 -$3000
16000 -$250

The macro would need to loop through each account and see If value in column C is negative or positive. My problem is that there are 200 different accounts, some should have positive values, others should have negative values. So when the amount for account 10110 is positive value then it is ok. In account 12100 should always be positive but their is an unnatural negative balance so the macro would highlight it. Not every trial balance will have all the accounts. Around half the accounts should have positive balances and the other half negative.

Is It possible to do this by a macro, as it is very tedious work doing it manually?

 

[JoeMo]

If you just want to highlight negative numbers in column C, why not use conditional formatting?

 

[shredda23]

Thanks for the reply I can't just highlight negative numbers because sometimes the number will be correct to be negative. Example. You credit the sales account so the number will be correct if it s negative. If sales amount is positive then it's an unnatural balance so the macro would highlight it.

 

[alfranco17]

You could check whether the account is credit or debit using a conditional formatting with a formula. Could you post a portion of your file?

 

[alfranco17]

Here is an example of how you could use a formula to look up the first two digits of the account to check whether it is credit or debit, and then use that info to know if it is right or wrong.

 

[shredda23]

Here is an example. If this could be done with a macro It would be great as It is very tedious going through checking manually If 10110 account is a credit then It is a unnatural balance etc.
Maybe an If statement might need to be coded in. IF account 10110 amount is negative then highlight, If not don't highlight? But with so many accounts it may not be possible?
[TABLE="width: 474"]
<COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3043" width=86><COL style="WIDTH: 141pt; mso-width-source: userset; mso-width-alt: 6684" width=188><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3669" width=103><COL style="WIDTH: 48pt" span=4 width=64><TBODY>[TR]
[TD="width: 377, bgcolor: transparent, colspan: 3"]This is an example of a quarter of most trial balances [/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 3"]I would need to go through maybe 100 of these a month[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]D[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]10200-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]Bank One PM Cash[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10,000.00[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]10300-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]Bank One To/From Owner[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]890,000.00[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]11000-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]A/R Rents[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"](32,000.00)[/TD]
[TD="bgcolor: transparent, colspan: 4"]These would need to be highlighted[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]11020-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]A/R Other[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]194,983.72[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]13010-0030[/TD]
[TD="class: xl65, bgcolor: transparent"]Prepaid Exp-Other[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]8,893.04[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]14001-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]Land - Acquisition[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]8,245,852.00[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]16000-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]Buildings[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]64,840.00[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]16001-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]Buildings - Acquisition[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]43,135,582.84[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]17000-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]Furniture, Fixtures & Equ[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]37,801.00[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]18000-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]Leasehold[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]641,644.64[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]18001-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]Leasehold - Acquisition[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]36,000.00[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]18310-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]T/E-Leasing Commissions[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50,000.00[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]18311-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]T/E Leasing Comm - Aquis[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]15,000.00[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]18320-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]T/E-Legal[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]89,000.00[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]18380-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]CIP - Bldg Improvement[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]207,275.58[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]18400-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]CIP - Tenant Improvement[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]54,654.00[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31000-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]Accrued Expenses-Capital[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]100,000.00[/TD]
[TD="bgcolor: transparent, colspan: 4"]These would need to be highlighted[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31100-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]Accrued Expenses-Taxes[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]25,000.00[/TD]
[TD="bgcolor: transparent, colspan: 4"]These would need to be highlighted[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]31200-0000[/TD]
[TD="class: xl65, bgcolor: transparent"]Accrued Expenses-Other[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"](452,714.34)[/TD]
[TD="bgcolor: transparent, colspan: 2"]Not hightlighted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

 

[alfranco17]

It can be done with a macro, but you don't need one.

Usually, account codes begin with 1 for assets, 2 for liabilities, 3 for capital, maybe 5 for expenses and 6 for income. Is that your case?

Your data pretty much matches the example I used. Give conditional formatting a try. You don't need a macro. If you can't come up with a rule for conditional formatting to work, you will not have a rule to create a macro.

 

[alfranco17]

One last thing. I am assuming you are using Excel 2007 or later. Which version are you using?