Format-Weekly Subtotal

[parteegolfer]

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


End Sub

[Duke Carey]

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


End Sub



"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
>
>
> End Sub
>
>
> --
> parteegolfer
> ------------------------------------------------------------------------
> parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951
> View this thread: http://www.excelforum.com/showthread...hreadid=517483
>
>

[parteegolfer]

This Did not work. Does anyone have any other idea for this problem.