# vba worsheet_change with calculation

Hello Experts:

I need help with an issue, but not sure if it can be done.

I want to monitor a set range and if the user enters a value greater than 50.00 -  then is would leave 50 in that targetcell and the remaining value in the cell immediately below it.

If Not Intersect(Target, Range("C20:G20, C22:G22, C24:G24, C26:G26")) Is Nothing Then
complete the calcuation........

In example: If C20 entered was 51.00 then C20 would change to 50.00 and C21 would be 1.00.

I will only be using positive numbers in the target range.

Any suggestions?
mike637

###### Who is Participating?

x
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.

Microsoft MVP ExcelCommented:
Hello,

something like

``````If Not Intersect(Target, Range("C20:G20, C22:G22, C24:G24, C26:G26")) Is Nothing Then

on error resume next
application.enableevents = false
If target > 50 then
target.offset(1,0) = target - 50
target = 50
End if
application.enableevents = true
End If
``````

cheers, teylyn
Microsoft MVP ExcelCommented:
All that wrapped in

Private Sub Worksheet_Change(ByVal Target As Range)

end sub

of course. Here's the complete sub

``````Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C20:G20, C22:G22, C24:G24, C26:G26")) Is Nothing Then

On Error Resume Next
Application.EnableEvents = False
If Target > 50 Then
Target.Offset(1, 0) = Target - 50
Target = 50
End If
Application.EnableEvents = True
End If
End Sub
``````

Experts Exchange Solution brought to you by