Back to original Formating

[parteegolfer]

I have entered this VBA to change a row color if a certain condition i
met. However if is condition then changes to something other then th
specified condion I would like the row format to change back to th
original. How can I get this done? Here is what I have to change th
row color:
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
Next
Next

End Sub


How can I get back to the original format. including the defaul
border

 

[Guest]

For each row, reserve a column (eg, Col CA) to contain the last color index,
reserve the another column (eg, CB) to contain the last border attribute, and
so on. Save the last attributes in these cells before you set it to the new
one. When you want to restore, do a pass and put the saved values into the
attribute again.

Regards,
Antonio Elinon

 

[Tom Ogilvy]

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 = xlNone
End If
Next
Next

When you set the interior colorindex to xlnone, the border will again be
visible.

--
Regards,
Tom Ogilvy



"parteegolfer" <[email protected]>
wrote in message
news[email protected]...

 

[parteegolfer]

I have entered the following and the row will not change to defaul
color when $A(whatever) is not equal to "Weekly Subtotal". It doe
change to orange when "Weekly Subtotal" is entered into a cell but won
change back to excel default color if cell is changed back to "". Wha
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 Su