Back to original Formating

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

parteegolfer - 28 Feb 2006 15:32 GMT

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

Reply to this Message

Antonio Elinon - 28 Feb 2006 16:00 GMT

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

> I have entered this VBA to change a row color if a certain condition is
> met. However if is condition then changes to something other then the
[quoted text clipped - 18 lines]
> How can I get back to the original format. including the default
> border?

Reply to this Message

Tom Ogilvy - 28 Feb 2006 16:03 GMT

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.

Signature

Regards,
Tom Ogilvy

Reply to this Message

parteegolfer - 28 Feb 2006 19:01 GMT

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

Reply to this Message