Hi Elaine,

You could do this with conditional formatting, and it would require no VBA
code. To do it, just follow these steps:

1) select your range (let's say A4:J60)

2) select Format | Conditional Formatting

3) select "Formula Is" and type the following into the textbox:

=AND(SEARCH("total",$E1)0,SEARCH("total",$E2)0)

4) click Format... and select the desired format (green pattern)

5) click OK, then OK


Now, a row should be shaded green when the values in column E 2 rows up and
3 rows up contain the string "total".

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


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