How can I run a Macro If any cell in col G:G contains a specific text Value

[norts55]

Hello,
This seems like an easy one but I have been searching and searching for similar code but just can't seem to find what I am looking for and get it to work.

Can someone please tell me how to Search my in active sheet, column G for the text value "My Text" and if "My Text" is found in any cell of column G, it runs the macro "My_Macro". If it does not find "My Text", it does nothing.

Any help would be appreciated as I have been struggling with this. I have code below that I think is close but I am missing something.

If (Range("G:G") = "My Text") Then
        Application.Run "'" & ActiveWorkbook.Name & "'" & "!My_Macro"
        End If

 

[Logit]

Option Explicit

Sub SrchColC()
    Dim rng As Range
    Dim shtSrc As Worksheet
    Dim c As Range
    Dim i As Integer
    
    
    With Application
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    
 'don't scan the entire column...
    Set rng = Application.Intersect(shtSrc.Range("G:G"), shtSrc.UsedRange)

    For Each c In rng.Cells
        If c.Value = "My Text" Then
            
             'Nameofyourmacrohere
          
            destRow = destRow + 1

        End If
    Next
    
     With Application
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
    
End Sub

 

[norts55]

Thank you very much for the help on the, I seem to be getting an error.

 

[Logit]

Try this :

Option Explicit

Sub Copy_n_Paste()
On Error Resume Next

    Dim srchtrm As String
    Dim rng As Range, destRow As Long
    Dim shtSrc As Worksheet
    Dim c As Range
    Dim i As Integer
    
    
    With Application
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    
    Set shtSrc = Sheets("Sheet1")    'source sheet

    'don't scan the entire column...
    Set rng = Application.Intersect(shtSrc.Range("G:G"), shtSrc.UsedRange)

    For Each c In rng.Cells
        If c.Value = "My Text" Then
        
         'Your Macro Name Here
            
            destRow = destRow + 1

        End If
    Next
    
    With Application
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
        
    Application.CutCopyMode = False
    Sheets("Sheet1").Range("A1").Select

End Sub

 

[kvsrinivasamurthy]

Try

Sub RunMacro()
Dim Frng As Range
Set Frng = Sheets("Sheet1").Range("G:G").Find("My text")
If Not Frng Is Nothing Then
Run "MyMacro"
End If
End Sub

 

[norts55]

Both Macros work great! Thank You!

 

[Logit]

You are welcome.