making a cell fixed number to a input number

[fwday]

The Dollars and the Percents are 2 diffrent cells. Its just that
sometimes I want to put in a percent and sometimes a dollar. For
example. in cell A1 is a dollar amount. In the Cell A2 is the dollar
amount of the pecentage of Cell B2. If I cange B2 it will change the
dollar amount. Then if I go back to A2 and put in a dollar amount the
formula for the equasion will be gone. I don't want it be be gone I
just want it to be in the background and change the percent amount.

I am sending an attachment so that you may understand what I am looking
for. I am making a worksheet to budget off of. Sometimes you budget by
dollars and sometimes you budget by percent. B7 is the sales. B8 is the
Wages. And C8 is the percent of wages. I want to have the option to
punch in the percent of wages that I am looking for or the dollars of
wages. If I make a typed change in cell B8 wages I want the C8 percent
to change. If I punch in the C8 percnet I want the wages to change.
This will give the flexiblity to budget eather way.

Here is a copy of the code that I got that worked. It worked fine. I
just don't know how to get it to work on many diffrent situations on
one sheet. You can only do one sheet_change in a page. How do I do this
for many diffrent places on one sheet?

paste in code like this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Errhandler
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B8:C8")) Is Nothing Then
If Len(Trim(Target.Value)) = 0 Then Exit Sub
Application.EnableEvents = False
If Target.Address = "$B$8" Then
Debug.Print 1
If IsNumeric(Target.Offset(-1, 0)) And _
Not IsEmpty(Target.Offset(-1, 0)) Then
Debug.Print 2
If Target.Offset(-1, 0).Value <> 0 Then
Debug.Print 3
Target.Offset(0, 1).Value = _
Target / Target.Offset(-1, 0)
End If
End If
Else
If IsNumeric(Target) Then
Target.Offset(0, -1) = Target.Offset(-1, -1) * _
Target
End If
End If
End If
Errhandler:
Application.EnableEvents = True
End Sub

File Attached: http://www.excelforum.com/attachment.php?postid=331791 (example.xls)

 

[Tom Ogilvy]

Here is the answer I gave you two days ago.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Errhandler
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Or Target.Column = 3 Then

If Len(Trim(Target.Value)) = 0 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 And Target.Row Mod 2 = 0 Then
If IsNumeric(Target.Offset(-1, 0)) And _
Not IsEmpty(Target.Offset(-1, 0)) Then
If Target.Offset(-1, 0).Value <> 0 Then
Target.Offset(0, 1).Value = _
Target / Target.Offset(-1, 0)
End If
End If
ElseIf Target.Column = 3 And Target.Row Mod 2 = 0 Then
If IsNumeric(Target) Then
Target.Offset(0, -1) = Target.Offset(-1, -1) * _
Target
End If
End If
End If
Errhandler:
Application.EnableEvents = True
End Sub

This assumes your wages row is an even row and your entry will either be in
column B (dollars) or in Column C (percent).

The Sales row will always be an odd row.

So it works on all cells in columns B and C