If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
Automatic display positive or negative
Copy and paste the below formula in C1 cell.
=IF(B1=0,52,IF(B10,-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. |
#4
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|