EXCEL VBA - Converting negative to positive and positive to negative

mike637
mike637 used Ask the Experts™
on
In excel vba I need to have my set range change negatives to positive numbers and negatives to positive.
I can not seem to get it to fire.

Here what I am have been trying:

Private Sub Worksheet_Change(ByVal target As Range)
   
    'On Error Resume Next
         
    If Not Intersect(target, Range("H3:H13")) Is Nothing Then
        Set rng = Intersect(target, Range("H3:H13"))
        For Each r In rng

        Me.Unprotect
       
        Application.EnableEvents = False
       
        target.Value = target.Value / 100
       
     Next r
       
    End If
       
           
    If Not Intersect(target, Range("R3:R13")) Is Nothing Then
      Set rng = Intersect(target, Range("R3:R13"))
        For Each cell In rng
      'Me.Unprotect
     
      'For Each c In target.Cells
          cell.Value = -cell.Value
          cell.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
      Next cell
           
      End If
     
           
      Application.EnableEvents = True
     
    'Me.Protect
   
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I realized I had to put the enable events to false for it to fire correctly.

mike

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial