Tom's is great, but it doesn't work if you have more than 1337 rows of
data.

Why would that be Jim?

--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote in message
...
Here is some code that works no matter how many rows of data you have.
Tom's
is great, but it doesn't work if you have more than 1337 rows of data.
Depends what you need.

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(2, -4), rngFound.Offset(2,
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