Can you suggest a more efficient way of doing this in VBA?

[zb134]

I have a table similar to what is shown

Col A (Item)	Col L (Committed Qty)	Col N (Available Qty)
A	5	2
B	2	3
C	1	1
A	2	2
D	1	4
B	2	3
C	1	1

<tbody>
</tbody>

What I need to do is simple, for each individual unique item (Col A)check if the Total of Col L> Total of Col N (to see if we have committed more than we have) and highlight the cell if it is.

I know how to do this using VBA my using two For loops and going through each cell in Col A and Check for a match in all other rows and then add up corresponding values in Col L and Col N.

But I have few thousand rows and this seems a very inefficient way of doing it.
Any suggestions?


Also, I know how to do this easily without VBA using sumif but that is not suited for my overall purpose.

 

[Momentman]

Have you considered using a pivot table?

 

[zb134]

No, I need to do it using VBA as it is part of a larger process.

 

[Eric Golf]

Hi,

How about using Conditional Formatting with Sumif() e.g.

=SUMIF($A$2:$A$8,$A2,$B$2:$B$8)>SUMIF($A$2:$A$8,$A2,$C$2:$C$8)

Hope this helps

Eric