as tom mentioned, your ranges are different. maybe do something like this, use
rng1 to set the range:

Option Explicit
Private Sub Workbook_Open()
Dim cell As Range, rng As Range, rng1 As Range
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
Set rng1 = Sh.Range("AL6:AL2000")

For Each cell In rng1
If cell.Value = "Weekly Subtotal" Then
Set rng = Intersect(rng1, _
cell.EntireRow)
rng.Interior.ColorIndex = 45

End If

If cell.Value = "" Then
Set rng = Intersect(rng1, _
cell.EntireRow)
rng.Interior.ColorIndex = xlNone
End If
Next
Next


End Sub

--


Gary


"parteegolfer" wrote
in message ...

Jim,

I entered this just like you suggested and I get the following error:

"OBJECT VARIABLE or WITH BLOCK NOT SET"

Not Sure what this means


--
parteegolfer
------------------------------------------------------------------------
parteegolfer's Profile:
http://www.excelforum.com/member.php...o&userid=31951
View this thread: http://www.excelforum.com/showthread...hreadid=517557