How to Run Macro if a column has a certain text in it

How to run a macro if a column has a certain text in it

Requirement:

The user wants a code to run only if any cell in column J has letter Y in it.

Solution:

Code:

Private Sub CommandButton2_Click()
    If WorksheetFunction.CountIf(Range("J:J"), "Y") <> 0 Then
        Dim I As Long
        Set ws = Worksheets("DESPATCHED")
        Application.ScreenUpdating = False
        If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
            ActiveSheet.ShowAllData
        End If
        Columns(10).AutoFilter 1, "Y"
        With Range("a4", Range("I" & Rows.Count).End(3))
            .Copy Sheet4.Cells(Rows.Count, 1).End(3).Offset(1)
            .EntireRow.Delete
        End With
        Columns(10).AutoFilter
   
        With Sheets("DESPATCHED")
        I = .Range("A" & .Rows.Count).End(xlUp).Row
        End With
        ws.Range("G2:G" & I).Value = "Y"
        Application.ScreenUpdating = True
    End If
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Mumps.