Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Back to original Formating
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 -- parteegolfe ----------------------------------------------------------------------- parteegolfer's Profile: http://www.excelforum.com/member.php...fo&userid=3195 View this thread: http://www.excelforum.com/showthread.php?threadid=51734 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Back to original Formating
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Back to original Formating
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" wrote in message news:parteegolfer.23y4cb_1141140906.7597@excelforu m-nospam.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Back to original Formating
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 -- parteegolfe ----------------------------------------------------------------------- parteegolfer's Profile: http://www.excelforum.com/member.php...fo&userid=3195 View this thread: http://www.excelforum.com/showthread.php?threadid=51734 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Going back to original location | Excel Discussion (Misc queries) | |||
Revert back to original sort | Excel Discussion (Misc queries) | |||
Pivot Tables - Can I get back to my original Data | Excel Discussion (Misc queries) | |||
Back to original file | Excel Programming | |||
using VBA to go back to the original value before the cell was changed | Excel Programming |