Your endif is in the wrong place. The if code is only reached if the cell
value is Weekly Subtotal. If it is not then the whole this is skipped. Try
this...

Private Sub Workbook_Open()
Dim cell As Range, rng As Range
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
For Each cell In Sh.Range("AL6:AL2000")
If cell.Value = "Weekly Subtotal" Then
Set rng = Intersect(Sh.Range("A8:J2000"), _
cell.EntireRow)
rng.Interior.ColorIndex = 45

end if

If cell.Value = "" Then
Set rng = Intersect(Sh.Range("A8:J2000"), _
cell.EntireRow)
rng.Interior.ColorIndex = xlNone
End If
Next
Next

--
HTH...

Jim Thomlinson


"parteegolfer" wrote:


I have entered the following and the row will not change to default
color when $A(whatever) is not equal to "Weekly Subtotal". It does
change to orange when "Weekly Subtotal" is entered into a cell but wont
change back to excel default color if cell is changed back to "". What
am i doing wrong!

Private Sub Workbook_Open()
Dim cell As Range, rng As Range
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
For Each cell In Sh.Range("AL6:AL2000")
If cell.Value = "Weekly Subtotal" Then
Set rng = Intersect(Sh.Range("A8:J2000"), _
cell.EntireRow)
rng.Interior.ColorIndex = 45
If cell.Value = "" Then
Set rng = Intersect(Sh.Range("A8:J2000"), _
cell.EntireRow)
rng.Interior.ColorIndex = xlNone
End If
End If
Next
Next


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