Alert in VBA if amount exceeds 100%

May I trouble anyone with this question.

I was helped with a previous question on this thread

http://www.excelforum.com/excel-work...ml#post2174267


If I already have lets say 90% on B9 and I try to enter 15% on D9 it wont allow me because of the total being over 100%, but how can I have an alert to say Please enter 10% or less.

Or

If I already have lets say 50% on B9 and I try to enter 20% on D9 and I try to enter 50% F9 it wont allow me because of the total being over 100%, but how can I have an alert to say Please enter 70% or less (the sum of B9+D9).

Can this be done using VBA?

I've attached the file that has the data validation.

Thanks..

One possible approach outlined below

Please Login or Register  to view this content.

The above should account for the fact that in reality multiple % values can be assigned simultaneously (ie highlighting F9, H9 - entering 60% and pressing CTRL + ENTER)

To insert the above

-- First remove the Data Validation on the cell range

-- Second right click on Tab against which the code is be applied - select View Code and paste above into resulting window ensuring Macros enabled thereafter.

The approach will in part depend on whether or not you want to permit user entry of multiple cells simultaneously... eg highlight B9,D9 or B9,B11 etc - enter % and press CTRL + ENTER ... do you want to permit / prevent this ?

Easier to prevent I think... perhaps the above will work for you ?

Please Login or Register  to view this content.

This code is to replace that provided previously

It doesn't work because the layout has changed since you told me of the additional requirements, ie:

Originally Posted by Preatorian

One question, I additional line entries that need the same alert.

On line B11,D11,F11,H11,J11,L11 as well as line 13,15,17,19, & 21,

The code I provided looked only at Odd Rows (11,13,15 etc...) whereas in the last incarnation the layout has altered (and does not appear to be consistent in terms of row spacing).... to cater change the following:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

(I noted you already revised the rngCells range per your layout)

That's all good - was just trying to explain the cause of the intermittent failure in the earlier iteration.

When I'm next in New Yoek you can buy me a bagel !

If I didn't have any empty lines and my data were all in rows one after the other like row 9 thru 40, no spaces in between, how can the code be changed to work accordingly...

Simply change the "valid" Range to

Please Login or Register  to view this content.

and then given you now know every row within the above range to be valid for testing you can remove the below line altogether

Please Login or Register  to view this content.

As for Bacalaito - sounds good - we Brits love our battered Fish !

P.S FWIW I miss NY too... not been over for a while...