Back to original Formating

[parteegolfer]

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 specified condion I would like the row format to change back to the original. How can I get this done? Here is what I have to change the 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 default border?

[Antonio Elinon]

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

"parteegolfer" wrote:

>
> 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
> specified condion I would like the row format to change back to the
> original. How can I get this done? Here is what I have to change the
> 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 default
> border?
>
>
> --
> parteegolfer
> ------------------------------------------------------------------------
> parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951
> View this thread: http://www.excelforum.com/showthread...hreadid=517341
>
>

[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]...
>
> 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
> specified condion I would like the row format to change back to the
> original. How can I get this done? Here is what I have to change the
> 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 default
> border?
>
>
> --
> parteegolfer
> ------------------------------------------------------------------------
> parteegolfer's Profile:
http://www.excelforum.com/member.php...o&userid=31951
> View this thread: http://www.excelforum.com/showthread...hreadid=517341
>

[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