Question: Goal:  

If finding the word "Total" in a cell, the current row with word "Total" as a start point (with letter D in the column) and all the way to the letter H shall have a light grey background color. If not having "Total", the background shall be default. 

Problem:  

How should I do it by using VBA?

![enter image description here](https://i.stack.imgur.com/BgwMm.gif)

Answer: 
You could try it with conditional formatting.
Just select the cell and go to "Format" -> "Conditional formatting" (well that's the path in Excel 2003, don't know 2007 or later)

But you can do it with a macro if you want to.
Here is an example how to do it:

```
Sub RowsToGrey()
Dim r As Long, i As Long
r = Cells(Rows.Count, 4).End(xlUp).Row
For i = 1 To r
    If InStr(Cells(i, 4), "Total") Then
        Range(Cells(i, 4), Cells(i, 8)).Interior.ColorIndex = 15
    Else
        Range(Cells(i, 4), Cells(i, 8)).Interior.ColorIndex = 0
    End If
Next i
End Sub

```

I hope that helps.
See [Debra's site](http://www.contextures.com/xlCondFormat01.html) for conditional formatting background. The actual approach to highlight only the active row with pure conditional formatting was actually quite tough - I got 99.9% there but I still needed a *damn* calculate event to refresh the formulas to make this work

FWIW, if you apply:

1. Apply this conditional formatting formula to your column `D:H` range  

`=AND(NOT(ISERROR(SEARCH("Total",$D7))),ROW(INDIRECT(CELL("address")))=ROW(INDIRECT(ADDRESS(ROW()+RAND()*0,COLUMN()))))`
2. Add a `Calcuate` Event by

Step 2

* right-click your sheet tab
* `View - Code`
* copy and paste in the code
below

*code*

```
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
End Sub

```

![enter image description here](https://i.stack.imgur.com/oxTim.gif)