Help with formula to convert numbers to negative

4lvis_bondarenko

Copper Contributor

Mar 05, 2021

MyQueries

Am tryin to make column U convert its value to negative if column S states "S" or if its in red.

Ive tried to add at the end of exsisting formula =IF(S3:S10001="S",*-1 but it didnt seem to work.

JMB17

Bronze Contributor

Mar 06, 2021

4lvis_bondarenko

If you are looking for a vba code, then you could try this. Right click on your worksheet tab, select view code, and paste this code into the code window that appears. Then, save the workbook as an *.xlsm (macro enabled workbook).

Private Sub Worksheet_Change(ByVal Target As Range)
     Dim exitReasonRng As Range: Set exitReasonRng = Me.Range("S:S")
     Dim exitPipsRng As Range: Set exitPipsRng = Me.Range("U:U")
     
     
     On Error GoTo ErrHandler
     Application.EnableEvents = False
     
     If Not Intersect(Target.EntireRow, Union(exitReasonRng, exitPipsRng)) Is Nothing Then
          If UCase(Intersect(Target.EntireRow, exitReasonRng).Value) = "S" Then
               With Intersect(Target.EntireRow, exitPipsRng)
                    If IsNumeric(.Value) And Len(.Value) > 0 Then
                         .Value = -Abs(.Value)
                    End If
               End With
          End If
     End If
     
     
ExitProc:
     Application.EnableEvents = True
     Exit Sub
     
ErrHandler:
     MsgBox "Error " & Err.Number & ": " & Err.Description
     Resume ExitProc
     
End Sub

Forum Discussion

Help with formula to convert numbers to negative

Resources