Hello,

I'm trying to build a macro for checking if an amount of shares which people say they own (and want to bring to a shareholders meeting) are indeed owned.
When someone is going to a General Shareholders Meeting they can go themselves and also bring a companion.
The people are identified by accountnumbers, the stock is identified by ISIN (Internation Securities Identification Number).

The amount of shares people want to use to vote at the General Shareholders Meeting should be checked against their actual ownership.
The amount they want to bring is in Excel workbook 1 which should contain the macro. And the amount they actually own is workbook 2. The name of workbook 2 differs per ISIN (stock) and I have already written the code to identify the correct file. But that is all I got .

The information for the first workbook looks like this.

Accountnumber - Name - Stock - ISIN - # shares - companion - # shares for companion
1379 Mr. Smith Google A US38259P5089 300
1508 Mr. Jones Boeing US0970231058 1
1508 Mr. Jones Boeing Company US0970231058 0 Mr. Dickens 1

So, Mr. Smith goes to the Google Shareholders Meeting and brings with him 300 shares. The macro should check the ISIN, go to the correct file (workbook) which holds the ownership information. In that file it should look for the accountnumber and on that row check the cell on column 'V' which holds the number of shares owned. So 300 should be smaller or equal to the cell on that row,column-V.
If the 300 number is bigger, it should color the cell which contains that number bright red . In this case Mr. Smith has 5000 shares, so nothing happens.

Now Mr. Jones goes to Boeing. He brings a friend. Both have 1 share, so the total is 2. The macro should check if Mr. Jones indeed ownes 2 shares or more. So, the number 2 should be smaller or equal to number of shares owned.
Unfortunately Mr. Jones only has 1 share. Now the cells for both the 'Mr. Jones'-rows holding the number of shares should color red. (In the example above, it should make the cell with the 1's red.)

The table file will be appended each day. So the macro should not run on the whole file, it should run it's checks on a selection. The selection contains the rows which have been appended for the day. With the selected cells the macro should, in my opinion:

1: Identify each ISIN
2: For each ISIN check the accountnumbers
3: For each accountnumber add the respective cells (so we have the total number of shares (X) a person wants to bring to the meeting).
4: For that ISIN, open the corresponding file (2nd workbook).
5: In that file, row 'G' contains the accountnumbers as text, so we should first do: Range("G:G") = Range("G:G").Value
6: For each accountnumber check the number of shares owned (Y)
7: Compare X and Y --> If X =< Y Then End Sub
8: Else = Highlight the cells holding numbers of shares in the first workbook.
9: Close the 2nd workbook (save changes is ok)
10: Rinse and repeat for each ISIN.

I have added an example file which contains the table above as well as the structure of the 2nd workbook.

Examplefile.xlsx

Below is the code I already have, for opening the correct file.


Please Login or Register  to view this content.