Sorry you wanted 2 rows down from the bottom. My other code is two rows down
from the top. Here is what you need... Sorry

Public Sub Shade()
Dim strFirstAddress As String
Dim rngFound As Range
Dim rngToSearch As Range

Set rngToSearch = ActiveSheet.Range("E1").EntireColumn

Set rngFound = rngToSearch.Find("total", , , xlPart)
If rngFound Is Nothing Then
MsgBox "No areas to shade."
Else
strFirstAddress = rngFound.Address
Do
If rngFound.Offset(1, 0).Value Like "*total*" Then
Range(rngFound.Offset(3, -4), rngFound.Offset(3,
5)).Interior.ColorIndex = 35
End If
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
End Sub

"Elaine" wrote:

I have an Excel 2002 spreadsheet where there are several records. I would
like to shade certain rows from Col A: Col J where the following occurs: if
current cell has the word total and the cell above has the word total in it,
I would like to shade two rows below current cell (with say green -- code 35).

Thus, if E42 = Book Total and E43 is June Total, I would like to shade A:J
in row 45.

If E42 = Book Total and E43 does not contain total then do nothing.

Tom Ogilvy and Jim Cone helped me in a previous problem similar to this but
I am stumped and don't know how to proceed from the point below. Any help is
really appreciated. Thanks.

Private Sub mcr18Shade()

Dim RngCell As Range
Dim lngCounter As Long

For lngCounter = 1337 To 1 Step -1
Set RngCell = ActiveSheet.Cells(lngCounter, 5) 'Col E

''If current cell in E has word total in it and cell above has total in it
then shade A:J

If RngCell.Value Like "*total*" And _
InStr(1, LCase(RngCell.Offset(-1, 0).Value), "total") 0 Then

'Shade two rows below green (interior.color=35)
End If
Next
End Sub