Macro to color quantity cells green when positive equals negative for ID and ACCOUNT

[YANECKC]

I have excel file with transactions with ID (7 bytes) and ACCOUNT (8 bytes) and QUANTITY (positives and negatives numbers).
The macro would
If there is the same ID and ACCOUNT and lets say QUANTITY five 100 shares positive and QUANTITY five 100 shares negative color those QUANTITY cells green.
If there is the same ID and ACCOUNT and lets say QUANTITY five 100 shares positive and QUANTITY four 100 shares negative color those QUANTITY cells red.
If there is the same ID and ACCOUNT and lets say QUANTITY five 100 shares positive and no QUANTITY negative color those QUANTITY cells red.

The macro would first match ID and ACCOUNT and then look at the quantity field. I used 100 shares as an example. Meaning there could be transactions for 5 shares or 75 shares , quantity field could be any amount as well as positive or negative.

 

[YANECKC]

I only need match cells with GREEN color in QUANTITY. All unmatched will have no color in QUANTITY.

 

[jolivanes]

Why are D10 & D11 not green? Same for D17 & D18.

 

[YANECKC]

View attachment 93052


I only need match cells with GREEN color in QUANTITY. All unmatched will have no color in QUANTITY.

Because for ID C844521 ACCOUNT 10180038 there are 2 transactions Positive QUANTITY 456 and 3 transactions Negative QUANTITY -456. I only want GREEN in cases where the Position QUANTITY has the same amount of transactions for Negative QUANTITY. I have to analysis cases where the negative QUANTITY transactions does not equal Positive QUANTITY transactions. On my end I have to look which Negative QUANTITY -456 transactions does not have Positive QUANTITY 456 transactions.

 

[Joe4]

Can you post your data in a manner that lets us copy it to our side (cannot do that from a screen print)?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Joe4]

OK, I think I was able to recreate a version of this, and get it to work.

If you select the cells D2:D21, and the apply this Conditional Formatting rule:

=COUNTIFS(A:A,A2,B:B,B2,C:C,C2,D:D,D2)=COUNTIFS(A:A,A2,B:B,B2,C:C,C2,D:D,-D2)

and choose your highlighting color, you will get this:

 

[YANECKC]

OK, I think I was able to recreate a version of this, and get it to work.

If you select the cells D2:D21, and the apply this Conditional Formatting rule:

=COUNTIFS(A:A,A2,B:B,B2,C:C,C2,D:D,D2)=COUNTIFS(A:A,A2,B:B,B2,C:C,C2,D:D,-D2)

and choose your highlighting color, you will get this:
View attachment 93104

Thank you

Formula works great !

 

[Joe4]

You are welcome.
Glad we were able to help!