Percentage Calculation Between Two Cells Using Code in VBA

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

Select allOpen in new window

EDIT Disable Events

Regards

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

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

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

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

ok just saw your post now and willing to help you but need some clarifications.

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

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

Select allOpen in new window

gowflow
percentage-V2.xls

Any chance to have tried the proposed solution ?
gowflow

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

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

Thanks goflow....that works perfectly

Edwin

Happy we got to it at the end and glad I could help. Sorry for the confusion in between
gowflow