Change the Number Charecter to negative formate

[sgmpatnaik]

Hi Good Morning


i have a small problem and i was stuck in there, that is


i want to change the Number to negative and Positive using with specific text


for that i create one code that is


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False


If Target.Column = 2 Then

If Range("A" & Target.Row) = "Damage" Or Range("A" & Target.Row) = "Shortage" Then

Target = -1 * Abs(Target)

End If

End If

Application.EnableEvents = True


End Sub


With This code i am getting the Negative Number with Specific Text but when i remove or delete the Specific Text then the Number is still in negative Mode but i want to change the number to Positive Mode


Kindly Help


Regards


SP

 

[NARAYANK991]

Hi Patnaik ,


Try this :

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 2 Then
If Range("A" & Target.Row) = "Damage" Or Range("A" & Target.Row) = "Shortage" Then
Application.EnableEvents = False
Target = -1 * Abs(Target)
End If
Else
If Range("A" & Target.Row) = "" And Range("B" & Target.Row) < 0 Then
Application.EnableEvents = False
Range("B" & Target.Row) = -1 * Range("B" & Target.Row)
Else
If Range("A" & Target.Row) = "Damage" Or Range("A" & Target.Row) = "Shortage" Then
Application.EnableEvents = False
Range("B" & Target.Row) = -1 * Range("B" & Target.Row)
End If
End If
End If
Application.EnableEvents = True

End Sub

[/pre]
Narayan

 

[sgmpatnaik]

Hi Narayank


Thanks It's Working great,


I have small doubt if we enter the Specific Text in Column A then automatically 0 appears in Column B Why


Thanks


SP

 

[NARAYANK991]

Hi Patnaik ,


Sorry for the oversight. Replace with this code :

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 2 Then
If Range("A" & Target.Row) = "Damage" Or Range("A" & Target.Row) = "Shortage" Then
Application.EnableEvents = False
If Target <> "" Then Target = -1 * Abs(Target)
End If
Else
If Range("A" & Target.Row) = "" And Range("B" & Target.Row) < 0 Then
Application.EnableEvents = False
Range("B" & Target.Row) = Abs(Range("B" & Target.Row))
Else
If Range("A" & Target.Row) = "Damage" Or Range("A" & Target.Row) = "Shortage" Then
Application.EnableEvents = False
If Range("B" & Target.Row) <> "" Then Range("B" & Target.Row) = -1 * Abs(Range("B" & Target.Row))
End If
End If
End If
Application.EnableEvents = True

End Sub

[/pre]
Narayan

 

[sgmpatnaik]

Thanks Naarayank


It's Working Fine


Thanks for your Support


Regards


SP