Excel 2007 : moving excess amount into different cell

pharma1,

Welcome to the forum!

What you're asking for can be accomplished a couple of ways. If you're okay with different cells changing, you can use formulas. If it has to be the cell you entered into changing, it would need to be a macro.

Attached is an example workbook that shows both solutions.

In the formula solution, you enter the number into cell A2. Cell B2 will show that number, maxing out at 50. Cell C2 will show the remainder (if any).
B2 formula: =MIN(A2,50)
C2 formula: =IF(A2<=50,"",A2-B2)

In the macro solution, you enter the number into cell A5. Cell A5 will remain that number if it is 50 or less, but will change to show the max, 50, if you enter a higher number. Cell B5 will show the remainder if any. This is accomplished using a Worksheet_Change event, and here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$A$5" Then
        Application.EnableEvents = False
        If Target.Value > 50 Then
            Target.Offset(, 1).Value = Target.Value - 50
            Target.Value = 50
        Else
            Target.Offset(, 1).ClearContents
        End If
        Application.EnableEvents = True
    End If
    
End Sub