# Percentage Calculation Between Two Cells Using Code in VBA

I have a set of cells that when combined need to equal 100%
As an example:
Considering cells B1 and B5

If I enter 55% in cell B1 then cell B5 automatically shows 45%

If I enter 25% in cell B5 then cell B1 automatically shows 75%

Thanks,

Edwin
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi,

pls try ( code to be placed in your worksheet module )

``````Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not (Intersect(Target, Range("B1")) Is Nothing) Then
Range("B5").Value = 1 - Range("B1").Value
ElseIf Not (Intersect(Target, Range("B5")) Is Nothing) Then
Range("B1").Value = 1 - Range("B5").Value
End If
Application.EnableEvents = True
End Sub
``````

EDIT Disable Events

Regards
0
Commented:
pls try this attached workbook in sheet1 put any value in cell b1 and see result in b5 and vice versa. Also this handles negatives if you put -2 you get -98 also it handles the formatting of the cell as previous code proposed assumes that your cell is formatted as percentage while if it is not you can get weired values as subtracting 1 from any number would not work as is 1 is in percentage.

Let me know
gowflow
percentage.xls
0
Author Commented:
Thanks Rgonzo,

That works great...not sure if it's proper to ask another question to build on this but how would I expand that to dividing 3 and 4 ways?

Thanks,

Edwin
0
Commented:
gixxer1020
Did you try my solution ?? what do oyu mean dividing 3 and 4 way ? like have 3 cells and 4 cells that all would add to 100% ???

gowflow
0
Author Commented:
Hi gowflow,

No I have not tried your code yet.
And, yes I may also need to have 3 or 4 cells that would all add to 100%

Thanks,

Edwin
0
Commented:

In your original post you had 2 cells and when 1 has a value the second has the complement to 100 that's fine.

Now we are talking about 4 cells say the following scenario:
Cell A1 = 70
Cell A2 blank
Cell A3 blank
Cell A4 blank

or any other combination
question:
Do you have a priority where to put the 30% or you want that 30% to be split equally in the 3 remaining cells or ... ??? pls clarify the process that you need the answer to be laided out.

gowflow
0
Commented:
Check this sample file what it does is any amount that you put in any cell will split the balance to 100% in the remaining 3 cells.

The cells concerned are A1,A2,A3,A4 and this can be modified depending on answering my previous request.

Note I have kept the routine for 2 cells as well it is in the sub Fill100In2Cells

here is the code for the new Sub for your easy reference all you need is to try the file and activate macros I have put in cell C4 the total of all A1,A2,A3 and A4 so it always add up tp 100%.

``````Sub Fill100In4Cells(target As Range)
Dim SplitVal As Double

Application.EnableEvents = False

If Not (Intersect(target, Range("B1")) Is Nothing) Then
Range("B1").NumberFormat = "0.00%"
Range("B2").NumberFormat = "0.00%"
Range("B3").NumberFormat = "0.00%"
Range("B4").NumberFormat = "0.00%"
If Range("B1").Value >= 0 Then
SplitVal = 1 - Range("B1").Value
Range("B2").Value = SplitVal / 3
Range("B3").Value = SplitVal / 3
Range("B4").Value = SplitVal / 3
Else
SplitVal = -Range("B1").Value - 1
Range("B2").Value = SplitVal / 3
Range("B3").Value = SplitVal / 3
Range("B4").Value = SplitVal / 3
End If
Else
If Not (Intersect(target, Range("B2")) Is Nothing) Then
Range("B1").NumberFormat = "0.00%"
Range("B2").NumberFormat = "0.00%"
Range("B3").NumberFormat = "0.00%"
Range("B4").NumberFormat = "0.00%"
If Range("B2").Value >= 0 Then
SplitVal = 1 - Range("B2").Value
Range("B1").Value = SplitVal / 3
Range("B3").Value = SplitVal / 3
Range("B4").Value = SplitVal / 3
Else
SplitVal = -Range("B2").Value - 1
Range("B1").Value = SplitVal / 3
Range("B3").Value = SplitVal / 3
Range("B4").Value = SplitVal / 3
End If

Else

If Not (Intersect(target, Range("B3")) Is Nothing) Then
Range("B1").NumberFormat = "0.00%"
Range("B2").NumberFormat = "0.00%"
Range("B3").NumberFormat = "0.00%"
Range("B4").NumberFormat = "0.00%"
If Range("B3").Value >= 0 Then
SplitVal = 1 - Range("B3").Value
Range("B1").Value = SplitVal / 3
Range("B2").Value = SplitVal / 3
Range("B4").Value = SplitVal / 3
Else
SplitVal = -Range("B3").Value - 1
Range("B1").Value = SplitVal / 3
Range("B2").Value = SplitVal / 3
Range("B4").Value = SplitVal / 3
End If

Else

If Not (Intersect(target, Range("B4")) Is Nothing) Then
Range("B1").NumberFormat = "0.00%"
Range("B2").NumberFormat = "0.00%"
Range("B3").NumberFormat = "0.00%"
Range("B4").NumberFormat = "0.00%"
If Range("B4").Value >= 0 Then
SplitVal = 1 - Range("B4").Value
Range("B1").Value = SplitVal / 3
Range("B2").Value = SplitVal / 3
Range("B3").Value = SplitVal / 3
Else
SplitVal = -Range("B4").Value - 1
Range("B1").Value = SplitVal / 3
Range("B2").Value = SplitVal / 3
Range("B3").Value = SplitVal / 3
End If
End If
End If
End If
End If

Application.EnableEvents = True

End Sub
``````
gowflow
percentage-V2.xls
0
Commented:
Any chance to have tried the proposed solution ?
gowflow
0
Author Commented:
Hi gowflow,

I've tried the code but it doesn't seem to work.
To answer your question here is an example of what I'm looking for.
In watching three cells, I would enter data in two cells and the third would automatically calculate so that all three cells combined total 100%.  However I would need to be able to enter data into any of the three cells.

Thanks,

Edwin
0
Commented:
ok pls give me what are the 3 cells A1,A2,A3 would be fine ???

Did you try the file I posted ??? percentage v2.xls ???? what do you mean it does not work ?? did you enable macros ?? it should work but maybe not what you want. Please clarify your statement so we can help you better. If you can reply fast we can solve your issue even faster.

Regards
gowflow
0
Commented:
ok I just noticed that  I maybe wrote something wrong in my previous file attached in ID: 39572938 I talked about The cells concerned are A1,A2,A3,A4 where infact I meant The cells concerned are B1,B2,B3,B4.

Anyway I just created an other file now that is attached where you just input in the colored cells B1, B2, B3 any value that you want and look at the rest interacting I have put in C3 a sum of all 3 cells that will give you the total at any time.

Pls check it try it and let me know.
gowflow
percentage-V3.xls
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks goflow....that works perfectly

Edwin
0
Commented:
Happy we got to it at the end and glad I could help. Sorry for the confusion in between
gowflow
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.