Change all cells in a column if a word is found

Question

I have a worksheet with 6 columns i want to match a word in column E and if it is in any cell in that column then replace the entire column with that word.

Sub Macro8()
    Columns("E:E").Select
    If Columns("E:E").Select = "dog" Then Entire.Columns("E:E").Select = "dog"
End Sub

Use `Worksheet_Change` Event. More about it [HERE](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) — Siddharth Rout, Sep 15 '14 at 07:58
Another [INTERESTING](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10718179#10718179) Read — Siddharth Rout, Sep 15 '14 at 08:06

score 1 · Accepted Answer · answered Sep 15 '14 at 08:02

Like I mentioned in the comments above, Use Worksheet_Change Event. However if you want to run a routine at your convenience then use this code

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find Lastrow in Col E which has data
        lRow = .Range("E" & .Rows.Count).End(xlUp).Row

        '~~> Use Countif to check for an occurance of Dog in Col E
        If Application.WorksheetFunction.CountIf(.Columns(5), "Dog") _
        Then .Range("E1:E" & lRow).Value = "Dog"
    End With
End Sub

Change all cells in a column if a word is found

1 Answers1