Sub test() Dim cfind As Range Set cfind = ActiveSheet.UsedRange.Cells.Find(what:="sun", lookat:=xlWhole) If Not cfind Is Nothing Then Range(Cells(6, cfind.Column), Cells(36, cfind.Column)).Interior.ColorIndex = 3 Else MsgBox "that word is not available in the sheet" End If End Sub
Private Sub Worksheet_Change(ByVal Target As Range) Dim cfind As Range, add As String ActiveSheet.Cells.Interior.ColorIndex = xlNone Set cfind = ActiveSheet.UsedRange.Cells.Find(what:="sun", lookat:=xlWhole) If Not cfind Is Nothing Then add = cfind.Address If Target.Column <> cfind.Column Then Exit Sub Range(Cells(6, cfind.Column), Cells(36, cfind.Column)).Interior.ColorIndex = 3 Else MsgBox "that word is not available in the sheet" End If End Sub
DON'T MISS