VBA - call function within With..End With

[logstarter]

Hi,

I have written something like

Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim ChangedRow As Integer
    With Sh
    
        If Not Intersect(Target, .Columns(3)) Is Nothing Then
            On Error GoTo bm_Safe_Exit
            Application.EnableEvents = False
            ChangedRow = Target.Row
            If WorksheetFunction.CountA(Target) > 0 Then
                confirmChange = MsgBox("Are you sure you want to assign the code?", vbYesNo + vbQuestion)
                If confirmChange = vbYes Then
                    Range("G" & ChangedRow) = createNo
                End If
            End If
        End If
        
        If Not Intersect(Target, .Columns(9)) Is Nothing Then
            On Error GoTo bm_Safe_Exit
            Application.EnableEvents = False
            ChangedRow = Target.Row
            If WorksheetFunction.CountA(Target) > 0 Then
                confirmChange = MsgBox("Are you sure you want to assign the code?", vbYesNo + vbQuestion)
                If confirmChange = vbYes Then
                    Range("M" & ChangedRow) = createNo
                End If
            End If
        End If

Since the code is repetitious, I would like to use a function like

Sub addNo(ByVal Sh As Object, ByVal Target As Range, ByVal col As Integer, ByVal NoCol As String)
  If Not Intersect(Target, .Columns(col)) Is Nothing Then
            On Error GoTo bm_Safe_Exit
            Application.EnableEvents = False
            ChangedRow = Target.Row
            If WorksheetFunction.CountA(Target) > 0 Then
                confirmChange = MsgBox("Are you sure you want to assign the code?", vbYesNo + vbQuestion)
                If confirmChange = vbYes Then
                    Range(NoCol & ChangedRow) = createNo
                End If
            End If
        End If
End Sub

My problem is that since there is

If Not Intersect(Target, [COLOR=#ff0000].Columns(col)[/COLOR]) Is Nothing Then

I would like to ask how to edit my function so that it could support .Column(col) with "With Sh".

Thank you.

 

[Fluff]

If you're always offsetting 4 columns, try

Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Dim ChangedRow As Integer
   
   If Not Intersect(Target, Sh.Range("C:C,I:I")) Is Nothing Then
      On Error GoTo bm_Safe_Exit
      Application.EnableEvents = False
      ChangedRow = Target.Row
      If WorksheetFunction.CountA(Target) > 0 Then
         confirmChange = MsgBox("Are you sure you want to assign the code?", vbYesNo + vbQuestion)
         If confirmChange = vbYes Then
            Target.Offset(, 4).Value = createNo
         End If
      End If
   End If
End Sub