# Formula or script to manipulate data in a cell

Hello, I need a formula or script to run in an Excel worksheet that if cell is greater than 40 I need that cell to be 40 and another cell to equal the difference. Example, cell A1 is 45, I want cell A1 to be 40 and Cell B1 to be 5. Is there a way I can do that or do I have to put the value 45 in a different cell, then have a formula in the other 2 cells referencing the one cell 45? Thanks
###### Who is Participating?

Commented:
Maybe this in the sheet module? Right-click the sheet tab, View Code and paste in. It is activated every time something in column A is changed,
``````Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
If Target.Value > 40 Then
Target.Offset(, 1) = Target - 40
Target = 40
End If
End If

End Sub
``````
0

Commented:
Display 45 in A1 display 40 in B1 and display 5 in C1. Data is crossreferenced and its impossible without programming. If you want to program you can have the value of in a variable find the difference display difference in B1 and then round value in A1
0

Commented:
Kept the "45" in A1
Formula in B1
=IF(A1>40,40,A1)
Formula in C1
=IF(A1>40,A1-B1,0)

See if it works out for you. See attached file.
40diff.xlsx
0

Author Commented:
Thanks for all the help, Both of the solutions worked. I am not sure which on i am going to implement in my spread sheet.

StephenJR - how would you select a range of cells in stead of the whole colunm? ie D259 thru D273

Thanks Again!
0

Commented:
My pleasure.

Change line 3 accordingly, e.g.:

``````If Not Intersect(Target,Range("D259:D273")) Is Nothing Then
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.