VBA Highlighting Row if Column A contains "Total"

[sbrandwe]

I am trying to highlight an entire row if the cell in Column A contains the text Total starting from row 5.

Any help anyone can give me, I would greatly appreciate.

Here is my current code:


Dim rng As Range
Dim c As Range
Dim endrow As Long
Dim endcol As Long


endrow = Range("A" & Rows.Count).End(xlUp).Row
endcol = Range("IV3").End(xlToLeft).Column


Set rng = Range(Cells(4, 1), Cells(endrow, endcol))


For i = 4 To endrow
If Range("A" & i).Value = "*Total*" Then
Rows(i).Select
Selection.Interior.Color = RGB(192, 192, 192)

End If

Next i

 

[Ivy_1011]

Hi Rick,

This worked when I tried it. How can I modify is so that I do not have to run the macro. Also, if I has different words that need to be color coded, for example "total", "qty", "sum" and I want a different color for each row highlighted. I change it to Worksheet_Chage so that it can work without running a macro button; but how can I add 2 more words to high light and two other other colors?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range, Addr As String

With Range("A1", Cells(Rows.count, "A").End(xlUp))
Set Cell = .Find("*total*", , xlValues, , , , False, , False)
'Set Cell = .Find("*S*", , xlValues, , , , False, , False)



If Not Cell Is Nothing Then
Addr = Cell.Address
Do
'Cell.EntireRow.Interior.Color = RGB(192, 192, 192)
Cell.Resize(, 8).Interior.Color = RGB(192, 192, 192)
'Cell.Resize(, 8).Interior.Color = RGB(192, 190, 191)
Set Cell = .FindNext(Cell)



Loop While Not Cell Is Nothing And Cell.Address <> Addr





End If
End With
End Sub

Also, how can I change the color of the font to another color?

 

[Ivy_1011]

Also, how can I change the color of the font to another color?