Automatic display positive or negative
 

Dear All, pls help me.
when I type $ 52.00 on C1, I want C1 display positive $52 or negative ($ 52)
, if B1 is positive or negative, I want the entire column C.
Exemple :
C1 : I type $52, it display for me $52 , because B1 is positive
C2 : I type $52, it display for me ($52) , because B2 is negative
B C
B1 5 $52
B2 (3) ($52)

It is possible ?
Thank you for your help.

Automatic display positive or negative
 

This can only be done with VBA and the code below will do it for you. To use
the code, open the workbook and go to the sheet you want this to work on and
then:
Right-click on the worksheet's name tab and choose [View Code] from the list.
Copy the code below and paste it into the code module presented to you in
the previous step.
Close the VB Editor and test it by making entries into columns B and C.

Note that you must make the entry into column B before you make the entry
into C. As written, it does not change the entry in C if you make a change
in B after an entry is already in C. If you change B, then you would need to
re-enter into C to see the change made.

Private Sub Worksheet_Change(ByVal Target As Range)
'only operates when a single cell in column C changes
If Target.Column < 3 Or _
Target.Cells.Count 1 Then
Exit Sub ' not in column C
End If
'if both B# and C# have entries
'then make C entry same sign (+/-) as B
If Not IsEmpty(Target) And _
Not IsEmpty(Target.Offset(0, -1)) Then
If Sgn(Target.Offset(0, -1)) < Sgn(Target) Then
Target = Target * -1
End If
End If
End Sub


"tran1728" wrote:

Dear All, pls help me.
when I type $ 52.00 on C1, I want C1 display positive $52 or negative ($ 52)
, if B1 is positive or negative, I want the entire column C.
Exemple :
C1 : I type $52, it display for me $52 , because B1 is positive
C2 : I type $52, it display for me ($52) , because B2 is negative
B C
B1 5 $52
B2 (3) ($52)

It is possible ?
Thank you for your help.

Automatic display positive or negative
 

Copy and paste the below formula in C1 cell.
=IF(B1=0,52,IF(B1<0,-52,""))

Select the entire C column do right clickFormat
CellsNumberCategoryCurrencySymbol Select $Negative
Numbers:select ($1,234.10) and give ok.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"tran1728" wrote:

Dear All, pls help me.
when I type $ 52.00 on C1, I want C1 display positive $52 or negative ($ 52)
, if B1 is positive or negative, I want the entire column C.
Exemple :
C1 : I type $52, it display for me $52 , because B1 is positive
C2 : I type $52, it display for me ($52) , because B2 is negative
B C
B1 5 $52
B2 (3) ($52)

It is possible ?
Thank you for your help.

Automatic display positive or negative
 

oh Yes, thank you so much.



"JLatham" wrote:

This can only be done with VBA and the code below will do it for you. To use
the code, open the workbook and go to the sheet you want this to work on and
then:
Right-click on the worksheet's name tab and choose [View Code] from the list.
Copy the code below and paste it into the code module presented to you in
the previous step.
Close the VB Editor and test it by making entries into columns B and C.

Note that you must make the entry into column B before you make the entry
into C. As written, it does not change the entry in C if you make a change
in B after an entry is already in C. If you change B, then you would need to
re-enter into C to see the change made.

Private Sub Worksheet_Change(ByVal Target As Range)
'only operates when a single cell in column C changes
If Target.Column < 3 Or _
Target.Cells.Count 1 Then
Exit Sub ' not in column C
End If
'if both B# and C# have entries
'then make C entry same sign (+/-) as B
If Not IsEmpty(Target) And _
Not IsEmpty(Target.Offset(0, -1)) Then
If Sgn(Target.Offset(0, -1)) < Sgn(Target) Then
Target = Target * -1
End If
End If
End Sub


"tran1728" wrote:

Dear All, pls help me.
when I type $ 52.00 on C1, I want C1 display positive $52 or negative ($ 52)
, if B1 is positive or negative, I want the entire column C.
Exemple :
C1 : I type $52, it display for me $52 , because B1 is positive
C2 : I type $52, it display for me ($52) , because B2 is negative
B C
B1 5 $52
B2 (3) ($52)

It is possible ?
Thank you for your help.