Looking to proof some code I added.

Hi guys,

Not great on VBA. I currently have this code in my work sheet.

Dim rw As Long, CopyTo  As String
Private Sub Worksheet_Change(ByVal Target As Range)
Dim thisrow As Long



  If Target.Count > 1 Then Exit Sub
   If Not Intersect(Target, Range("AB:AC")) Is Nothing Then
      With Target
         If UCase(.Value) = "BLOCK" Then
            If .Column = 28 Then
               CopyTo = "Banc ***"
               rw = Sheets(CopyTo).Range("E" & Rows.Count).End(xlUp).Row + 1
               Range("B" & .Row).Copy
                      Sheets(CopyTo).Range("E" & rw).PasteSpecial (xlPasteValues)
               Range("AB" & .Row).Copy
                      Sheets(CopyTo).Range("f" & rw).PasteSpecial (xlPasteValues)
               Sheets(CopyTo).Range("J" & rw).Value = "Sent Up"   'Put "Sent Up" in Column J
            ElseIf .Column = 29 Then
               CopyTo = "Home Ins"
               rw = Sheets(CopyTo).Range("D" & Rows.Count).End(xlUp).Row + 1
               Range("B" & .Row & ":C" & .Row).Copy
                     Sheets(CopyTo).Range("D" & rw).PasteSpecial (xlPasteValues)
               Range("AC" & .Row).Copy
                      Sheets(CopyTo).Range("F" & rw).PasteSpecial (xlPasteValues)
               Range("D" & .Row).Copy
                      Sheets(CopyTo).Range("g" & rw).PasteSpecial (xlPasteValues)
               Sheets(CopyTo).Range("J" & rw).Value = "Sent Up"   'Put "Sent Up" in Column K

            End If
         End If
      End With
   End If
   Const sPW            As String = "$AJ$1"
   Const sHide          As String = "Aa:Aa, Ak:Ak, Ap:Ap, AQ:AQ, Av:Av, Aw:Aw, Bb:Bb, Bc:Bc, Bh:Bh, Bi:Bi, Bn:Bn, Bo:Bo, Bt:Bt, Bu:Bu, Bz:Bz, Ca:Ca "
   If Not Intersect(Target, Range(sPW)) Is Nothing Then
      If Target.Value = 1234 Then
         
         'Range(sHide & 1).EntireColumn.Hidden = False
         Range(sHide).EntireColumn.Hidden = False
      
      ElseIf Target.Value = "" Then
    
         
         'Range(sHide & 1).EntireColumn.Hidden = True
         Range(sHide).EntireColumn.Hidden = True
      End If
   End If

    If Target.Column = 24 Then
      
      If Range("AB" & Target.Row) = "Block" Then
         lr = shtBASS.Range("B" & Rows.Count).End(xlUp).Row
         test = Sheets("Mort Figs").Range("B" & Target.Row).Value & Sheets("Mort Figs").Range("AB" & Target.Row).Value
         For x = 5 To lr
            test2 = shtBASS.Range("E" & x).Value & shtBASS.Range("F" & x).Value
            If test2 = test And Target.Value = "" And shtBASS.Range("J" & x) = "Issued" Then
                shtBASS.Range("J" & x) = "Sent Up"
                shtBASS.Range("K" & x) = ""
                Exit For
            ElseIf test2 = test And shtBASS.Range("J" & x) = "Sent Up" Then
                shtBASS.Range("J" & x) = "Issued"
                shtBASS.Range("K" & x) = Target.Value
               Exit For
            End If
         Next
End If

      
      If Range("AC" & Target.Row) = "Block" Then
         lr = shtHIN.Range("B" & Rows.Count).End(xlUp).Row
         test = Sheets("Mort Figs").Range("B" & Target.Row).Value & Sheets("Mort Figs").Range("AC" & Target.Row).Value
         For x = 5 To lr
            test2 = shtHIN.Range("D" & x).Value & shtHIN.Range("F" & x).Value
            If test2 = test And Target.Value = "" And shtHIN.Range("J" & x) = "Issued" Then
                shtHIN.Range("J" & x) = "Sent Up"
                shtHIN.Range("K" & x) = ""
                Exit For
            ElseIf test2 = test And shtHIN.Range("J" & x) = "Sent Up" Then
                shtHIN.Range("j" & x) = "Issued"
                shtHIN.Range("k" & x) = Target.Value
               Exit For
            End If
         Next
      End If

   End If
   
 If Target.Column = 6 Then
   Application.EnableEvents = False
   thisrow = Target.Row
   If Range("A" & thisrow) = "" Then Range("A" & thisrow) = Date
   
   Application.EnableEvents = True
   End If
   
   If Range("AK" & Target.Row) = "Not Going Ahead" Then
   Application.EnableEvents = False
   thisrow = Target.Row
   If Range("E" & thisrow) = "" Then Range("E" & thisrow) = "None"
   Application.EnableEvents = True
   End If
    
    
   If Target.Column = 23 Then
   Application.EnableEvents = False
   thisrow = Target.Row
   Range("T" & thisrow).ClearContents
   
   Application.EnableEvents = True
   End If  End Sub

I have recently added the bit in red. It seems to be working but it was a bit of a guess from previous codes I've been given. Can any one see if there are any potential bugs in the bit I added. Really cannot get my head around the application enable events bit.

Thanks a mil

Nitro

Ignore .

Cool. Seems to be working ok.