If then else needed in Excel 2007

[Legal Learning]

Ok, here's a reall good one: Here is what I need:

Cell C3 may or may not contain text. If it does, then I want cell C4's
numeric data (which is currently a positive number) to be a negative number
instead.

Example:

C3=CR
C4=100.00
I want cell C4 to be -100.00 instead of 100.00

Does this make sense?
Any Excel heads out there that can help will be GREATLY appreciated.

CG

Right now I have

 

[Don Guillett]

Right click sheet tab>view code>insert this.

Private Sub Worksheet_Change(ByVal Target As Range)
'On Error Resume Next
If Target.Address <> Range("c3").Address Then Exit Sub
If Not IsNumeric(Target) Then
If IsNumeric(Target.Offset(1)) Then
Target.Offset(1) = -Abs(Target.Offset(1))
End If
End If
End Sub

 

[Bernard Liengme]

A formula cannot change the value in another cell
You could put this into C5: =IF(ISTEXT(C3),-C4,C4)

The code below may help.
Copy it; right click the worksheet tab and select View Code; paste the
subroutine
But it has no way of knowing it the value has been changed previously

Sub Worksheet_change(ByVal Target As Range)
If Target.Address <> Range("C3").Address Then Exit Sub
If WorksheetFunction.IsText(Target) Then
Range("c4") = Range("C4") * -1
Else
Range("c4") = Range("C4")
End If
End Sub

best wishes

 

[Jacob Skaria]

--If you have a formula in C4 which returns 100; then you can modify the
formula to validate the entry in C3 and return a positive or negative
number....

--If there is no formula in C4; then you would need to make use of the
worksheet event to do this.Select the sheet tab which you want to work with.
Right click the sheet tab and click on 'View Code'. This will launch VBE.
Paste the below code to the right blank portion. Get back to to workbook and
try out.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("C3")) Is Nothing Then
Application.EnableEvents = False
Range("C4") = Abs(Range("C4"))
If IsNumeric(Range("C3")) = False Then Range("C4") = -(Abs(Range("C4")))
Application.EnableEvents = True
End If
End Sub


If this post helps click Yes