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?

This is an example of a quarter of most trial balances
I would need to go through maybe 100 of these a month
A	B	C	D
10200-0000	Bank One PM Cash	10,000.00	Not hightlighted
10300-0000	Bank One To/From Owner	890,000.00	Not hightlighted
11000-0000	A/R Rents	(32,000.00)	These would need to be highlighted
11020-0000	A/R Other	194,983.72	Not hightlighted
13010-0030	Prepaid Exp-Other	8,893.04	Not hightlighted
14001-0000	Land - Acquisition	8,245,852.00	Not hightlighted
16000-0000	Buildings	64,840.00	Not hightlighted
16001-0000	Buildings - Acquisition	43,135,582.84	Not hightlighted
17000-0000	Furniture, Fixtures & Equ	37,801.00	Not hightlighted
18000-0000	Leasehold	641,644.64	Not hightlighted
18001-0000	Leasehold - Acquisition	36,000.00	Not hightlighted
18310-0000	T/E-Leasing Commissions	50,000.00	Not hightlighted
18311-0000	T/E Leasing Comm - Aquis	15,000.00	Not hightlighted
18320-0000	T/E-Legal	89,000.00	Not hightlighted
18380-0000	CIP - Bldg Improvement	207,275.58	Not hightlighted
18400-0000	CIP - Tenant Improvement	54,654.00	Not hightlighted
31000-0000	Accrued Expenses-Capital	100,000.00	These would need to be highlighted
31100-0000	Accrued Expenses-Taxes	25,000.00	These would need to be highlighted
31200-0000	Accrued Expenses-Other	(452,714.34)	Not hightlighted

<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>
</TBODY>

 

[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?