Compile two Worksheet_Change code into one

Unexpc

Board Regular
Joined
Nov 12, 2020
Messages
134
Office Version
  1. 2019
Platform
  1. Windows
Hi guys
I have two code of Worksheet_Change
One of them make negative number and other, doing a macro code
How compile this two into one?
Code 1
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E3:E1048576,G3:G1048576")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value > 0 Then Target = Target.Value * -1
End Sub
Code 2
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
    If Target.Count = 1 And Target.Column = 2 And Target.Row >= 67 Then
        If (Target.Row - 34) Mod 32 = 0 Then
           Application.EnableEvents = False
           Application.ScreenUpdating = False
            For i = 2 To 30840 Step 1
                  If Sheets("Sheet1").Range("B" & (34 - 2) * i + 2).Value <> "" Then
                     Sheets("Sheet2").Range("A" & 34 * (i - 1) + 7 & ":H" & 34 * i + 6).Copy Sheets("Sheet2").Range("A" & 34 * i + 7)
                 Else
                     Sheets("Sheet2").Visible = False
                    Application.ScreenUpdating = True
                    Application.EnableEvents = True
                    Exit Sub
               End If
          Next i
     End If
   End If
End Sub
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("E3:E1048576,G3:G1048576")) Is Nothing Then         
        If Target.Value > 0 Then Target = Target.Value * -1
    End If
    If Target.Count = 1 And Target.Column = 2 And Target.Row >= 67 Then
        If (Target.Row - 34) Mod 32 = 0 Then
           Application.EnableEvents = False
           Application.ScreenUpdating = False
            For i = 2 To 30840 Step 1
                  If Sheets("Sheet1").Range("B" & (34 - 2) * i + 2).Value <> "" Then
                     Sheets("Sheet2").Range("A" & 34 * (i - 1) + 7 & ":H" & 34 * i + 6).Copy Sheets("Sheet2").Range("A" & 34 * i + 7)
                 Else
                     Sheets("Sheet2").Visible = False
                    Application.ScreenUpdating = True
                    Application.EnableEvents = True
                    Exit Sub
               End If
          Next i
     End If
   End If
End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,122,752
Messages
5,597,921
Members
414,190
Latest member
PuzzlerUK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top