How to Highlight rows containing particular keyword in VBA?

Question

I'm working with MS Excel 2010 VBA.i want to write a code for highlighting the rows which contains the keyword canada i have written the following code,

       Cells.Find(What:="Canada", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,         SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
      ActiveCell.EntireRow.Select
      With Selection.Font
             .Color = -16776961
             .TintAndShade = 0
      End With

i want to use the code in a loop to highlight all the rows which contains the keyword "canada".How can i do it using VBA?

use [`FindNext`](http://msdn.microsoft.com/en-us/library/office/ff196143(v=office.15).aspx). And also read [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Dmitry Pavliv, May 06 '14 at 08:53

Ripster · Answer 1 · 2014-05-06T06:57:33.043

Example:

Sub Highlight()
    Dim vCell As Range

    'Loop through every used cell in the active worksheet
    For Each vCell In ActiveSheet.UsedRange
        'If the cells value contains "Canada" change its font to red
        If InStr(vCell.Value, "Canada") Then
            vCell.Font.Color = -16776961
        End If
    Next
End Sub

Here is a link to microsofts office site explaining how loops work:
http://office.microsoft.com/en-us/training/get-in-the-loop-with-excel-macros-RZ001150634.aspx?section=11

How to Highlight rows containing particular keyword in VBA?

1 Answers1